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

From: <qazmlp1209_at_rediffmail.com>
Date: 23 Jan 2007 07:12:53 -0800
Message-ID: <1169565173.908183.60010@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 - 09:12:53 CST