Comparing 2 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

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing 2 tables

Author

Topic

tristanlee85
Starting Member

3 Posts

Posted - 2008-07-14 : 08:44:14

Hey everyone. I've got a question about comparing 2 tables. For starters, let me give you a layout of my tables:

Lic_SongPubSplits.SplitID
=========================
1
2
3
4
5
6
7
8

Lic_Expirations.SplitID
=========================
1
3
4
7
8

The first table is my "master" table so anything in table 2 is 100% in table 1, but not everything in table 1 is in table 2. I need a query that will return the SplitID that is missing from table 2 when comparing it to table one. Here is what I have so far:

SELECT     Lic_Expirations.SplitID
FROM         Lic_Expirations
WHERE     (((Lic_Expirations.SplitID) NOT IN
                          (SELECT     Lic_SongPubSplits.SplitID
                            FROM          Lic_SongPubSplits INNER JOIN
                                                   Lic_Expirations ON Lic_SongPubSplits.SplitID = Lic_Expirations.SplitID)))
UNION
SELECT     Lic_SongPubSplits.SplitID
FROM         Lic_SongPubSplits
WHERE     (((Lic_SongPubSplits.SplitID) NOT IN
                          (SELECT     Lic_SongPubSplits.SplitID
                            FROM          Lic_SongPubSplits INNER JOIN
                                                   Lic_Expirations ON Lic_SongPubSplits.SplitID = Lic_Expirations.SplitID)))

This query seems to return all records from the first table, not the missing records from the second table. Any ideas?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-14 : 08:48:47

i think you're just over complicating it. look at outer joins in Books online

select *
from Lic_SongPubSplits t1
left join Lic_Expirations t2 on t1.SplitID = t2.SplitID
where t2.SplitID is null

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 08:49:28

[code]SELECT a.SplitID
FROM Lic_SongPubSplits AS a
LEFT JOIN Lic_Expirations AS b ON b.SplitID = a.SplitID
WHERE b.SplitID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 08:53:42

[code]SELECT TableName,
MAX(SplitID) AS SplitID
FROM (
SELECT 'Lic_SongPubSplits' AS TableName,
SplitID
FROM Lic_SongPubSplits

UNION ALL

SELECT 'Lic_Expirations',
SplitID
FROM Lic_Expirations
) AS d
GROUP BY TableName
HAVING COUNT(*) = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

tristanlee85
Starting Member

3 Posts

Posted - 2008-07-14 : 08:59:22

Wow. I was over-complicating it. Thank you all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources