Intersect two sets and return complement

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)

Intersect two sets and return complement

Author

Topic

mayhem
Starting Member

11 Posts

Posted - 2004-03-12 : 05:08:57

Hi, I'm wondering whether this can be done. I want to compare the results of two queries, say two lists of names, then return those names that are in one set and not the other (most of the names should be in both sets).

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-03-12 : 07:20:12

Surely you would need to use an outer join. Take your pick!

________________
Make love not war!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-12 : 08:54:11

Make a third query that puts creates a couple temp tables and executes the results of your first two queries into the tables. You can then do use a select with a left outer join like Amethystium said to get your final result.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mayhem
Starting Member

11 Posts

Posted - 2004-03-12 : 23:46:35

I don't quite understand. Suppose I have two tables #group1 and #group2, with only one field each called NAMES, and there entries of #group2 is a smaller subset of #group1. If I do an outer join:


SELECT #group1.NAMES FROM #group1 LEFT OUTER JOIN
#group2

that'll just give me all the names in #group1 multiplied by the number of entries in #group2.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-12 : 23:55:20

Try:

SELECT #group1.names, #group2.names
FROM #group1 FULL JOIN #group2 on #group1.names=#group2.names
WHERE #group1.names IS NULL OR #group2.names IS NULL

The full join will get all rows from both tables, and the WHERE clause will eliminate rows where they match.

Jeff has a more elegant solution for this somewhere, using a UNION I believe, but I can't find it.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-13 : 00:07:34

It would be something like...

SELECT names FROM
(
SELECT names from #group1
UNION ALL
SELECT names from #group2
) a
GROUP BY names

HAVING Count(*) = 1

maybe ?

Damian

mayhem
Starting Member

11 Posts

Posted - 2004-03-13 : 00:31:43

Thanks a lot, it works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources