Re: intersection - set theory!!

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 2005 Forums

Transact-SQL (2005)

Re: intersection - set theory!!

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-11-30 : 21:40:38

Hi,

I have following situation. I need to get a result set based on the 2 different kind of inputs. I don't have the SQL query details at this point to share but any examples of how to do this would be very helpful as a starting point. Please note that 1, 2 .. are distinct records based on some criteria.

Ex A={1, 2, 3, 4} ; B={1,4,6,7} ; Therefore: AnB={1,4}
A={1, 2, 3, 4} ; B={6,7} ; Therefore AnB={1,2,3,4}

How can I get this contrary result set. Any ideas would be helpful at this point!!

SA

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-01 : 02:52:45

You can get these using joins..

In first case you can use INNER JOIN,

SELECT A.value AS AnB FROM A INNER JOIN B ON A.value = B.value

And in 2nd case you can use LEFT JOIN,

SELECT A.value AS AnB FROM A LEFT JOIN B ON A.value = B.value

I have posted this based on my assumption.. If you need exact result as per your need, then post the table structure and sample values..

Balaji.K

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-12-01 : 14:47:19

select Name,id from product
E8400 NULL
E8600 0

select Name,id from product2
E8600 1
E8200 0

Queries:
-- 0 records
SELECT Name, id
FROM product
where id = 0
intersect
SELECT Name, id
FROM product2
where id = 1
-------------------------------------------
--1 records
SELECT Name, id --1
FROM product2
where id = 1
except
SELECT Name, id --0
FROM product
where id = null

----------------------------

Please note that this is a simple scenario but I want to combine these 2 queries to create 1 result set which displays intersect if something is in common and if there is nothing in common then dispalys the original result set as A shown here. Please advice.

Ex A={1, 2, 3, 4} ; B={1,4,6,7} ; Therefore: AnB={1,4} the result of intersection
A={1, 2, 3, 4} ; B={6,7} ; Therefore AnB={1,2,3,4} the result set from except.

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources