Returning non-matching records between 2 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

General SQL Server Forums

New to SQL Server Programming

Returning non-matching records between 2 tables.

Author

Topic

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-27 : 16:38:20

Hi. I am really new to SQL Server 2005 and to SQL in general. I have the following SQL statement that I'm working with:

Select tblSessionID.SessionID from tblSessionID join tblSession on tblSessionID.SessionID = tblSession.SessionID

in the where clause, I want to be able to return all records that do no match. When I do where tblSession.SessionID <> tblSessionID.SessionID I get no results. I should get back 1243 rows. What am I doing wrong, any ideas?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 17:09:11

you can do it in may ways:


select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

or

select SessionID from tblSessionID
EXCEPT
select sessionID from tblSession

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-27 : 17:43:08

Hanbing's solution works if you are looking for all teh records that do not exists in one table. However, if you are looking for records that do not exists in both tables you would take a slightly different approach. Here is one way to do that:

DECLARE @SessionID TABLE (SessionID INT)
INSERT @SessionID
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 4
UNION ALL SELECT 6
UNION ALL SELECT 7

DECLARE @Session TABLE (SessionID INT)
INSERT @Session
SELECT 1
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7


SELECT 
	COALESCE(A.SessionID, B.SessionID)
FROM 
	@SessionID AS A
FULL OUTER JOIN 
	@Session AS B
	ON A.SessionID = B.SessionID
WHERE
	A.SessionID IS NULL
	OR B.SessionID IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 19:10:41

SELECT SessionID FROM (
SELECT DISTINCT SessionID FROM @SessionID
UNION ALL
SELECT DISTINCT SessionID FROM @Session
) AS d
GROUP BY SessionID
HAVING COUNT(*) = 1

E 12°55'05.63"
N 56°04'39.26"

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-28 : 08:45:54

quote:

Originally posted by hanbingl

you can do it in may ways:


select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

or

select SessionID from tblSessionID
EXCEPT
select sessionID from tblSession

Thanks hanbingl. This does work and returns the correct amount of rows. Thanks for the help.

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-28 : 08:49:46

quote:
Originally posted by Lamprey

Hanbing's solution works if you are looking for all teh records that do not exists in one table. However, if you are looking for records that do not exists in both tables you would take a slightly different approach. Here is one way to do that:
DECLARE @SessionID TABLE (SessionID INT)
INSERT @SessionID
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 4
UNION ALL SELECT 6
UNION ALL SELECT 7

DECLARE @Session TABLE (SessionID INT)
INSERT @Session
SELECT 1
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7

SELECT
COALESCE(A.SessionID, B.SessionID)
FROM
@SessionID AS A
FULL OUTER JOIN
@Session AS B
ON A.SessionID = B.SessionID
WHERE
A.SessionID IS NULL
OR B.SessionID IS NULL

Hi Lamprey, thanks for your help, but I can't get this to work. (I'm fairly new to SQL and I think I may just not understand how to use the above statement.) It returns 4 rows, but it doesn't appear to come from the correct tables.

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-28 : 08:50:33

quote:
Originally posted by Peso

SELECT SessionID FROM (
SELECT DISTINCT SessionID FROM @SessionID
UNION ALL
SELECT DISTINCT SessionID FROM @Session
) AS d
GROUP BY SessionID
HAVING COUNT(*) > 1

E 12°55'05.63"
N 56°04'39.26"

Hi Peso. Thanks for your help, but this actually appears to return too many rows.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 09:55:46

I saw that now. I changed the > 1 to = 1 which is correct.

E 12°55'05.63"
N 56°04'39.26"

hollyquinn
Starting Member

31 Posts

Posted - 2008-10-28 : 10:01:59

quote:
Originally posted by Peso

I saw that now. I changed the > 1 to = 1 which is correct.

E 12°55'05.63"
N 56°04'39.26"

Yep, that works. Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-28 : 12:12:30

quote:
Originally posted by hollyquinn

Hi Lamprey, thanks for your help, but I can't get this to work. (I'm fairly new to SQL and I think I may just not understand how to use the above statement.) It returns 4 rows, but it doesn't appear to come from the correct tables.

In order to get it to work you will need to change the table names and column names to your table and column names. I just created some table varibales and sample data to work with.

ThangT
Starting Member

2 Posts

Posted - 2008-10-28 : 12:25:47

Hi,
I am also new.

Can you please explain this query?

select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

First of all, what is the "1" for? and second, can I replace the "=" for "<>" ?

I have this query:

SELECT count(p.id_person) from db.dbo.person_similar_artists p
where not exists
(select 1 from db2.dbo.person pp
where pp.id_person = p.id_person )

Either "=" or "<>" will return the same result (0)

Thanks

quote:

Originally posted by hanbingl

you can do it in may ways:


select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

or

select SessionID from tblSessionID
EXCEPT
select sessionID from tblSession

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 14:58:35

"1" doesn't mean anything. as long as EXISTS subquery returns the results with matching IDs what returned doesn't matter. So to minimize fetch, I hardcode it to "1".

No you can't replace "=" with "<>" because it will each row in table 1 will <> another row in table 2, double negative will result every row to match. (I hope this is clear...)
1 1
2 3
4 5
6 6
7 7
1 = 1, 1 <> 3, 1 <> 5, 1 <> 6, 1 <> 7 (with "=" you'll get 1=1 back, "<>" will result 1<>3, 1<>5, 1<>6, 1<>7 and so on...)

quote:
Originally posted by ThangT

Hi,
I am also new.

Can you please explain this query?

select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

First of all, what is the "1" for? and second, can I replace the "=" for "<>" ?

I have this query:

SELECT count(p.id_person) from db.dbo.person_similar_artists p
where not exists
(select 1 from db2.dbo.person pp
where pp.id_person = p.id_person )

Either "=" or "<>" will return the same result (0)

Thanks

quote:
Originally posted by hanbingl

you can do it in may ways:

select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

or

select SessionID from tblSessionID
EXCEPT
select sessionID from tblSession

ThangT
Starting Member

2 Posts

Posted - 2008-10-29 : 11:48:46

Thanks, Hanbingl.

quote:
Originally posted by hanbingl

"1" doesn't mean anything. as long as EXISTS subquery returns the results with matching IDs what returned doesn't matter. So to minimize fetch, I hardcode it to "1".

No you can't replace "=" with "<>" because it will each row in table 1 will <> another row in table 2, double negative will result every row to match. (I hope this is clear...)
1 1
2 3
4 5
6 6
7 7
1 = 1, 1 <> 3, 1 <> 5, 1 <> 6, 1 <> 7 (with "=" you'll get 1=1 back, "<>" will result 1<>3, 1<>5, 1<>6, 1<>7 and so on...)

quote:
Originally posted by ThangT

Hi,
I am also new.

Can you please explain this query?

select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

First of all, what is the "1" for? and second, can I replace the "=" for "<>" ?

I have this query:

SELECT count(p.id_person) from db.dbo.person_similar_artists p
where not exists
(select 1 from db2.dbo.person pp
where pp.id_person = p.id_person )

Either "=" or "<>" will return the same result (0)

Thanks

quote:
Originally posted by hanbingl

you can do it in may ways:

select SessionID from tblsessionID where not exists (select 1 from tblSession where tblsession.sessionid = tblsessionid.sessionid)

or

select SessionID from tblSessionID
EXCEPT
select sessionID from tblSession

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources