Select with EXCEPT Statement - 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

Transact-SQL (2000)

Select with EXCEPT Statement

Author

Topic

suchiate
Starting Member

33 Posts

Posted - 2007-03-14 : 06:29:16

Hey guys,

I have a problem with selecting multiple values using the Except statement. I have two tables, one is a lookup table and another one is the actual data. I want to select the data from my lookup which does not exist in my actual data.

Something like

Select GroupID, GroupDesc from Lookup EXCEPT
(Select GroupID from tblData where Field = Condition)

I wan to display the Description as well... how can i do it?

Thanks alot in advance, hope to hear from you guys soon.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:35:28

You must use SQL Server 2005 to use EXCEPT keyword.
In SQL Server 2000 you can use LEFT JOIN

SELECT		GroupID,
		GroupDesc
FROM		Lookup
LEFT JOIN	tblData ON tblData.GroupID = Lookup.GroupID AND tblData.Field = Condition
WHERE		tblData.GroupID IS NULL

You can also play around with NOT EXISTS and NOT IN if you want to.

Peter Larsson
Helsingborg, Sweden

suchiate
Starting Member

33 Posts

Posted - 2007-03-14 : 06:40:39

yea i am using SQL 2005 Express,

and thanks alot it is working now with your previous post reference

=)

can you teach me how it would look like if i were to use Except to do the same thing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:44:08

[code]SELECT GroupID
FROM Lookup

EXCEPT

SELECT GroupID
FROM tblData
WHERE Field = Condition[/code]

Peter Larsson
Helsingborg, Sweden

suchiate
Starting Member

33 Posts

Posted - 2007-03-14 : 06:50:03

Yea that's what I posted initially, i mean what would the except look like if i wanted the Description out as well?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:53:38

[code]
SELECT x.GroupID,
y.GroupDesc
FROM (
SELECT GroupID
FROM Lookup

EXCEPT

SELECT GroupID
FROM tblData
WHERE Field = Condition
) AS x
INNER JOIN Lookup AS y ON y.GroupID = x.GroupID[/code]
Peter Larsson
Helsingborg, Sweden

Peter Larsson
Helsingborg, Sweden

suchiate
Starting Member

33 Posts

Posted - 2007-03-14 : 06:58:43

Wow!

Thanks alot, in terms of performance-wise,

LEFT join will be better in this case right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 07:07:25

It might.
It depends if there are duplicates in the tblData table.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources