Doing an INTERSECT without INTERSECT

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

Doing an INTERSECT without INTERSECT

Author

Topic

kburns
Starting Member

1 Post

Posted - 2011-01-25 : 18:13:14

Hi there -

I am trying to solve a problem that I am not sure is even solvable in SQL. Here is the situation...

I have a view (called address_mappings) that maps many to many. It has userid to addresses they have lived at. For example:

user_id - address
--------------------
userid1 - 123 main st
userid1 - 36 nimrod pl
userid1 - 10 12th st
userid2 - 123 main st
userid2 - 69 69th st
userid3 - 123 main st

In this example, all three users lived at 123 main st at one point in their lives.

I would like to get an intersection of the people who lived in a certain SET of addresses. (ie: How many people have lived at address1, address2 and address3?)

(I know this can be done with INTERSECT)

HOWEVER (and this is crucial), I would like to handle this by passing in an array of address. ie: "... IN ('address1','address2','address3')"

My first thought was to start with:

SELECT * from address_mappings WHEN address_mappings.address IN ('address1','address2','address3')

At this point I know the number of addresses I am looking for is 3. So if I can somehow GROUP BY the userid, I know that any group of size 3 represents a user who has lived at all 3 addresses.

Is there anyway to do something like this, perhaps using some 1337 combination of GROUP BY, INTERSECT, DISTINCT, SUM?

Thank you so much for your help. Hopefully my explanation makes sense. Please ask any questions.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-26 : 01:45:49

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 for how to split your list into a dataset.
What you are after is called Relational Division, and I have blogged a thing or two about it here

http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Relational-division.aspx
http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 01:48:32

"How many people have lived at address1, address2 and address3?"

Check the below query:

Select
Address,Count(UserId)
From Address_Mappings
Where Address in (Address1,Address2,Address3,...,AddressN)
Group by Address

Cheers!
MIK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-26 : 01:53:48

MIK, OP wants to know which people who have lived on ALL three addresses.

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 02:02:55

Peso! yes could be, but I replied as per my perception via the highlighted text of the OP ("How Many"). I just replied one the portion of OPs query. and you did another one where i think the OP is facing problems too .. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources