Multiple joins - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple joins

Author

Topic

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2004-08-31 : 02:42:00

Hi All,

I have 2 tables (table A and table B) and I had to find based on unique ID in both tables - I use SQL 2000:
- all values, which are in table A but are not present in table B;
- all values, which are in table B but are not present in table A;
- all values, which are in both tables;

What is the most elegant and fastest way, if it is possible to make in one query instead of 3 separate batches?

I appreciate all propolsals and advices.
Thanks in advance.

The Rebel

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-31 : 03:09:18

Depends, do you want the output to say which of the three options it falls into, or not?

If not -

SELECT * from tablea where idcolumn = youridvalue
UNION ALL
SELECT * from tableb where idcolumn = youridvalue

Or if so

SELECT 'Aonly' AS 'whichtable', a.* from tablea a left outer join tableb b on a.idcolumn = b.idcolumn where b.idcolumn is null

UNION ALL

SELECT 'Bonly' AS 'whichtable', b.* from tableb b left outer join tablea a on a.idcolumn = b.idcolumn where a.idcolumn is null

UNION ALL

SELECT 'Both' AS 'whichtable', a.* from tablea a inner join tableb b on a.idcolumn = b.idcolumn

-------
Moo. :)

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2004-09-01 : 03:11:11

Tnx but I expected a query like a mixture of joins in one batch. Is there anybody who has an idea if it is possibel to do that?

quote:
Originally posted by mr_mist

Depends, do you want the output to say which of the three options it falls into, or not?

If not -

SELECT * from tablea where idcolumn = youridvalue
UNION ALL
SELECT * from tableb where idcolumn = youridvalue

Or if so

SELECT 'Aonly' AS 'whichtable', a.* from tablea a left outer join tableb b on a.idcolumn = b.idcolumn where b.idcolumn is null

UNION ALL

SELECT 'Bonly' AS 'whichtable', b.* from tableb b left outer join tablea a on a.idcolumn = b.idcolumn where a.idcolumn is null

UNION ALL

SELECT 'Both' AS 'whichtable', a.* from tablea a inner join tableb b on a.idcolumn = b.idcolumn

-------
Moo. :)

The Rebel

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-01 : 16:55:07

Select ID, MAX(TableName) as TableName, COUNT(*) as Count
FROM
(
select A.ID, 'Table A' as TableName
From A
union all
Select B.ID, 'Table B' as TableName
From B
) q
GROUP BY ID

the results will look something like this:

ID,TableName,Count
1,TableA,1
2,TableB,1
3,TableB,2

for each ID returned:
if Count = 1, the ID exists only in TableName
if count = 2, the ID exists in both tables (and tableName returned is irrelevant)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources