Re: [Oracle 9i] Nested SQL query involving multiple tables!

select col_a_1, col_b_2, null
from
(
select col_a_1, col_b_2, col_C_2, count(col_a_1) over (partition by col_a_1 order by col_a_1) as data_count
from tab1
join tab2 on (col_a_1 = col_a_2)
where col_b_1 = 'true'
)
where data_count = 1

hth

<qazmlp1209_at_rediffmail.com> wrote in message news:1169565173.908183.60010_at_s48g2000cws.googlegroups.com...
> have two tables:
> Table-1, with the columns Col-a1, Col-b1 and some other columns
> Table-2, with the columns Col-a2, Col-b2, Col-c2 and some other
> columns
>
> Here are the tables filled with some sample record values:
>
> Table-1
> =====
> Col-a1 Col-b1
> ------- --------
> 120 true
> 121 true
> 234 false
> 98700 true
>
> Table-2
> =====
> Col-a2 Col-b2 Col-c2
> --------- --------- --------
> 120 10 Val1
> 120 20 Val2
> 121 15 Val1
> 234 35 Val3
> 98700 255 Val3
> 98700 355 Val3
>
>
> I would like to do the following using the SQL:
> - select the Col-a1 values in the Table-1 where Col-b1=true . For each
> of these, select the records in the Table-2 where there is only one
> association between Col-a2, Col-b2. For those records, set the Col-c2
> value to null.
>
> For the above sample records, only the following record
> 121 15 Val1
> should become
> 121 15 null
>
> What can be the best performant query to achieve this? I tried with
> some queries, but I get errors like "single-row subquery returns more
> than one row". Hence, I require your help.
>
Received on Tue Jan 23 2007 - 10:07:36 CST