Join tables - 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

Join tables

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-03-13 : 20:31:25

Hello all.

I want to see if all the id number in a temp table (#idtemp) that does not have a match in either table a or table b. Does the codes below met what I'm trying to achieve?

SELECT DISTINCT #idtemp.id AS ID, a.id AS A_ID, b.id AS B_ID
FROM #idtemp
LEFT JOIN employee a
ON #idtemp.id = a.id
LEFT JOIN salary b
ON #idtemp.id = b.id
WHERE a.id IS NULL OR b.id IS NULL

Please help.

Thanks.

siumui

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-13 : 21:27:57

[code]
SELECT DISTINCT #idtemp.id AS ID ~~, a.id AS A_ID, b.id AS B_ID~~
FROM #idtemp
LEFT JOIN employee a ON #idtemp.id = a.id
LEFT JOIN salary b ON #idtemp.id = b.id
WHERE a.id IS NULL
OR b.id IS NULL
[/code]

OR using not exists

[code]
SELECT DISTINCT t.id
FROM #idtemp t
WHERE NOT EXISTS
(
SELECT *
FROM employee x
WHERE x.id = t.id
)
OR NOT EXISTS
(
SELECT *
FROM salary x
WHERE x.id = t.id
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources