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

[qazml...@rediffmail.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.

[sybrandb]

This is not the place to get a free Oracle course for people who
- don't want to read manuals
- don't want to do their own homework.
This is especially not the place to have someone else do your work for
free.

PLEASE go away, and find a different playground.

--
Sybrand Bakker
Senior Oracle DBA

[gazzag]

qazml...@rediffmail.com wrote:
> 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

[deletia]

> I tried with some queries, but I get errors like "single-row subquery returns more
> than one row". Hence, I require your help.

If you post some examples of what you've tried, I'm sure people would
be more inclined to help.

HTH

-g

[sybrandb]

On Jan 23, 4:47 pm, "gazzag" <gar...@jamms.org> wrote:

> qazmlp1...@rediffmail.com wrote:
> > 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[deletia]
>
> > I tried with some queries, but I get errors like "single-row subquery returns more

> > than one row". Hence, I require your help.If you post some examples of what you've tried, I'm sure people would

> be more inclined to help.
>
> HTH
>
> -g

I don't think the OP tried at all. He merely want someone else to do
his homework.
Just look at the quick sucesssion in which he is posting elementary
questions, on which he obviously didn't do anything to resolve them
himself.
Probably OTS is too slow for him, so that is the reason why he is
posting this here.

[cc]

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

<qazml...@rediffmail.com> wrote in message
news:1169565173....@s48g2000cws.googlegroups.com...