Hi Tom,


SQL> select * from customer_det;

CID NAME DOP
---------- -------------------- ---------
1 Chandan 01-JAN-84
2 Ramu 01-MAR-84
3 Raghavendra 17-MAR-83
4 Kumar 17-FEB-85
5 Prasad 25-FEB-85
6 Harshavardan 29-JUL-86
7 Vardan 09-JUN-86
8 Varaprasad 07-AUG-82
9 Pradeep 07-AUG-82
10 sudeep 03-AUG-82

10 rows selected.

SQL> select * from orders_det;

CID OID NAME
---------- ---------- --------------------
1 1 Chandan
2 2 Ramu
3 4 Raghavendra
4 3 Kumar
5 9 Prasad
6 7 Harshavardan
7 5 Vardan
8 8 Varaprasad
9 6 Pradeep
10 10 sudeep
8 11 Varaprasad
2 12 Ramu
5 13 Prasad
10 14 sudeep
7 15 Vardan

15 rows selected.

SQL> select * from payment_det;

OID PA CID
---------- -- ----------
8 Y 3
3 Y 5
1 Y 2
15 Y 9
10 N 8
10 N 6
11 N 6
7 Y 1
SQL> select a.cid,a.name,a.dop,b.cid,b.name,c.payed
2 from customer_det a,orders_det b,payment_det c
3 where a.cid=b.cid and b.cid=c.cid and c.cid=a.cid;

CID NAME DOP CID NAME PA
---------- -------------------- --------- ---------- -------------------- --
1 Chandan 01-JAN-84 1 Chandan Y
2 Ramu 01-MAR-84 2 Ramu Y
3 Raghavendra 17-MAR-83 3 Raghavendra Y
5 Prasad 25-FEB-85 5 Prasad Y
6 Harshavardan 29-JUL-86 6 Harshavardan N
6 Harshavardan 29-JUL-86 6 Harshavardan N
8 Varaprasad 07-AUG-82 8 Varaprasad N
9 Pradeep 07-AUG-82 9 Pradeep Y
8 Varaprasad 07-AUG-82 8 Varaprasad N
2 Ramu 01-MAR-84 2 Ramu Y
5 Prasad 25-FEB-85 5 Prasad Y

11 rows selected.


For the above query how to rewrite using sub query.

Hi Connor,

I firstly thank you for your wonderful work which helped me a lot in learning Oracle concepts.

The below query is written in join, Now i would like to rewrite the same query using sub query. how to do this. i don't no how to select different table columns in first select itself as we do in joins. Please help me by writing the below query in sub-queries.

SQL> select a.cid,a.name,a.dop,b.cid,b.name,c.payed
2 from customer_det a,orders_det b,payment_det c
3 where a.cid=b.cid and b.cid=c.cid and c.cid=a.cid;


regards,
Gajanana