Many to many - exclusive result

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

Many to many - exclusive result

Author

Topic

JayWright
Starting Member

1 Post

Posted - 2007-10-17 : 07:36:43

A problem about many to many relationships from an SQL beginner. Here's a contrived abstract example, as I'd prefer not to give away specifics.

Imagine I have two tables: users, food
The relationship (to like) is many-to-many so I've got a link table, which might look like the below:

andrew, apples
bob, banana
bob, apples
chris, carrots
chris, apples
chris, banana

I want to select users who like bananas and apples exclusively.

The answer should be 'bob' ONLY. select * from users inner join food on <IDs> where food in ('bananas','apples') isn't suitable , because it'll also return 'chris' who should be disqualified (because he also likes carrots).

Apart from potentially being bad DB design (although this is an abstract example; I do have ID numbers), can anyone suggest how to get this in a scalable way?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 07:39:00

Try

Select users from table
where food in ('banana','apples')
group by users
having count(*)=2

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 07:40:23

or

Select users from table
where food in ('banana','apples')
group by users
having count(distinct food)=2

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 09:23:00

madhivanan -- that will include Chris in the results; he wants only people returned that like exactly those 2 items, and no more.

There may be a more efficient way (Peso?), but here's one idea:

select users
from table
group by users
having count(*) = 2 and sum(case when food in ('banana','apples') then 1 else 0 end) = 2

that assumes a pk of (users/food). If there is no constraint on those columns in the results (there really should be!) then it gets a little more complicated:

select users
from (select distinct users, food from table) x
group by users
having count(*) = 2 and sum(case when food in ('banana','apples') then 1 else 0 end) = 2

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 10:19:31

Thanks Jeff. I missed out that point

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 10:52:24

[code]DECLARE @Sample TABLE (People VARCHAR(6), Fruit VARCHAR(7))

INSERT @Sample
SELECT 'andrew', 'apples' UNION ALL
SELECT 'andrew', 'apples' UNION ALL
SELECT 'bob', 'banana' UNION ALL
SELECT 'bob', 'apples' UNION ALL
SELECT 'chris', 'carrots' UNION ALL
SELECT 'chris', 'apples' UNION ALL
SELECT 'chris', 'banana'

-- Madhi
Select people
from @sample
where fruit in ('banana','apples')
group by people
having count(distinct fruit) = 2

-- jeff
select people
from @sample
group by people
having count(*) = 2 and sum(case when fruit in ('banana', 'apples') then 1 else 0 end) = 2

-- jeff 2
select people
from (select distinct people, fruit from @sample) x
group by people
having count(*) = 2 and sum(case when fruit in ('banana','apples') then 1 else 0 end) = 2

-- Peso
SELECT People
FROM (
SELECT People,
CASE
WHEN Fruit = 'Banana' THEN 1
WHEN Fruit = 'Apples' THEN 2
ELSE 0
END AS theFruit
FROM @Sample
) AS d
GROUP BY People
HAVING MIN(theFruit) = 1
AND MAX(theFruit) = 2[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 11:15:56

A scalable way to get what you want?

-- Prepare sample data
DECLARE	@Sample TABLE (People VARCHAR(6), Fruit VARCHAR(7))

INSERT	@Sample
SELECT	'eric', 'grapes' UNION ALL
SELECT	'eric', 'carrots' UNION ALL
SELECT	'andrew', 'apples' UNION ALL
SELECT	'andrew', 'apples' UNION ALL
SELECT	'bob', 'banana' UNION ALL
SELECT	'bob', 'apples' UNION ALL
SELECT	'chris', 'carrots' UNION ALL
SELECT	'chris', 'apples' UNION ALL
SELECT	'chris', 'banana'

-- Peso 2
SELECT		People
FROM		(
			SELECT	People,
				CASE
					WHEN Fruit = 'Banana' THEN 1
					WHEN Fruit = 'Apples' THEN 2
					WHEN Fruit = 'Grapes' THEN 4
					WHEN Fruit = 'Carrots' THEN 8
					ELSE 0
				END AS theFruit
			FROM	@Sample
		) AS d
GROUP BY	People
HAVING		MIN(theFruit) > 0
		AND SUM(DISTINCT theFruit) = 3

/* Will get you Bob
		AND SUM(DISTINCT theFruit) = 3
*/
/* Will get you Cris
		AND SUM(DISTINCT theFruit) = 11
*/
/* Will get you Andrew
		AND SUM(DISTINCT theFruit) = 2
*/

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 11:19:17

[code]-- Prepare sample data
DECLARE @Sample TABLE (People VARCHAR(6), Fruit VARCHAR(7))

INSERT @Sample
SELECT 'eric', 'grapes' UNION ALL
SELECT 'eric', 'carrots' UNION ALL
SELECT 'andrew', 'apples' UNION ALL
SELECT 'andrew', 'apples' UNION ALL
SELECT 'bob', 'banana' UNION ALL
SELECT 'bob', 'apples' UNION ALL
SELECT 'chris', 'carrots' UNION ALL
SELECT 'chris', 'apples' UNION ALL
SELECT 'chris', 'banana'

-- Setup available food
DECLARE @Food TABLE (ID INT, FoodName VARCHAR(7))

INSERT @Food
SELECT 1, 'Banana' UNION ALL
SELECT 2, 'Apples' UNION ALL
SELECT 4, 'Grapes' UNION ALL
SELECT 8, 'Carrots'

-- Peso 3
SELECT People
FROM (
SELECT s.People,
COALESCE(f.ID, 0) AS theFood
FROM @Sample AS s
LEFT JOIN @Food AS f ON f.FoodName = s.Fruit
) AS d
GROUP BY People
HAVING MIN(theFood) > 0
AND SUM(DISTINCT theFood) = 3[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 12:57:12

quote:

INSERT @Sample
SELECT 'andrew', 'apples' UNION ALL
SELECT 'andrew', 'apples' UNION ALL
SELECT 'bob', 'banana' UNION ALL
SELECT 'bob', 'apples' UNION ALL
SELECT 'chris', 'carrots' UNION ALL
SELECT 'chris', 'apples' UNION ALL
SELECT 'chris', 'banana'

Be careful coding up or optimizing for situations or data that may not be possible; we should really find out for sure what the constraints are on this table, we have no way of knowing if that data is valid.

As for more generic solutions, that is the advantage of the one I posted, it is easily adaptable to as many fruits as you will ever need, which Min() and Max() unfortunately will not do.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources