problem with join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with join

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-03-07 : 02:25:18

i have 2 tables items and purchase_details.
Items table contain :- item_id, name, desc
purchase table contain :- pid, item_id, price

i want list of item name which is not there in purchase table

eg.
item table is as follow
item_id name desc
1 test nkj
2 test1 kjjk
3 t jhvj
4 test3 kgjhg
5 ggvg hbhj

and purchase table contain

pid item_id price
1 3 40
2 4 50
3 4 50

i want output as

item_id
1
2
5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-07 : 02:30:40

[code]select i.item_id
from items i
where not exists (select * from purchase_details p where p.item_id = i.item_id)

or

select i.item_id
from items i left join purchase_details p
on i.item_id = p.item_id
where p.item_id is null[/code]

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources