Query involving multiple tables ...

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query involving multiple tables ...

Author

Topic

chasseml
Starting Member

2 Posts

Posted - 2009-03-20 : 12:30:20

Hello,

I have two tables:

TableA
------------
aKey char(10)
...

TableB
-------------
aKey char(10)
...

I need to select all values of "aKey" in TableB that do not exist in TableA.

Now, say I have tables TableB-TableZ, and for each I need to execute that same query (with TableA) and consolidate the results.

Thanks for any suggestions / best practice tips,

chasse

chasseml
Starting Member

2 Posts

Posted - 2009-03-20 : 12:40:45

Actually, I just need an answer to the first part:

I need to select all values of "aKey" in TableB that do not exist in TableA.

Thx!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:41:40

[code]
SELECT t.aKey,other fields...
FROM
(
SELECT aKey,fields FROM TableB
UNION ALL
SELECT aKey,fields FROM TableC
UNION ALL
....
SELECT aKey,fields FROM TableZ
)t
LEFT JOIN TableA a
ON a.aKey=t.aKey
WHERE a.aKey IS NULL
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-22 : 00:01:01

try this one too


SELECT t.aKey,other fields...
FROM
(
SELECT aKey,fields FROM TableB
UNION ALL
SELECT aKey,fields FROM TableC
UNION ALL
....
SELECT aKey,fields FROM TableZ
)t WHERE akey NOT IN (SELECT akey FROM TableA )

SELECT t.aKey,other fields...
FROM
(
SELECT aKey,fields FROM TableB
UNION ALL
SELECT aKey,fields FROM TableC
UNION ALL
....
SELECT aKey,fields FROM TableZ
)t WHERE NOT EXISTS (SELECT * FROM TableA WHERE akey = t.akey )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources