You need to use a "left outer join" as below:

Select [master table].[Itemcode],child1.price,child2.price
from [master table] left outer join child1 on [master
table].[itemcode]=child1.itemcode
left outer join child2 on [master table].[itemcode]=child2.itemcode

Brian Freeman
Carnegie Technology and Bluewave Computing
(xxx) xxx-xxxx  ext. 415
www.carnegie.com   / www.bluewave-computing.com


-----Original Message-----
From: Shankar [mailto:shankargo@y...]
Sent: Friday, November 22, 2002 12:46 PM
To: sql language
Subject: [sql_language] Selecting Items from Multiple table.


Hi,

Here ItemCode is a Primary Key. ItemCode in Child1 table will not be
present in Child2 table. We need to get a output like if the price of
the item code belonging to same parts and same item is equal, then we
need to give the value in the output. we need to check in Child1
table
for the presence of multiple itemcodes with same price belonging to
same part and same item and also compare child1 and child2 for the
same
constraint and give the result. I tried with this. But it gives zero
items selected. 

Master Table:
ItemCode  Item  Parts
---------------------
100001    1001  2000
100002    1001  2000
100003    1001  3000
100004    1001  3000
100005    1001  3000
100006    1001  3000
100007    1001  4000
100008    1001  5000
100009    1002  5000
100010    1002  6000
 
Child1:-
ItemCode  Price
---------------
100001    30000
100002    30000
100005    40000
100006    50000
100007    70000
100009    80000

Child2:-
ItemCode  Price
---------------
100003    40000
100004    40000
100008    80000

Output:-
ItemCode Child1.Price Child2.Price
----------------------------------
100001    30000
100002    30000
100003                  40000
100004                  40000
100005    40000

Thanks,
Sankar

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com