CODEWITHSUNDEEP
mysqlsql
Ronald McDonald
Ronald McDonald

Reputation: 15

How to get an exact result of my sql query in a link table

Im trying to get the groups where the brand_id's match exactly to the group.

I've tried this solution (see fiddle) but when i try to obtain one item that has a brand id that is assigned to all the groups it shows all three groups. But it should match zero, because its not an exact match.

http://sqlfiddle.com/#!9/a5b16e/2/0

I was wondering if there is a solution to this

SELECT  group_id as groep
FROM    mailgroups a
WHERE   a.brand_id IN (2)
GROUP BY a.group_id
HAVING COUNT(*) = 1

this should return group 3

SELECT  group_id as groep
FROM    mailgroups a
WHERE   a.brand_id IN (2, 1)
GROUP BY a.group_id
HAVING COUNT(*) = 2

and this should only return group_id 1

Upvotes: 0

Views: 85

Answers (2)

forpas
forpas

Reputation: 164099

You don't need subqueries or joins.
Set the correct conditions in the having clause and remove the where clause:

SELECT  group_id as groep
FROM    mailgroups 
GROUP BY group_id
HAVING SUM(brand_id IN (2)) = 1 AND SUM(brand_id NOT IN (2)) = 0;

SELECT  group_id as groep
FROM    mailgroups 
GROUP BY group_id
HAVING SUM(brand_id IN (1, 2)) = 2 AND SUM(brand_id NOT IN (1, 2)) = 0;

See the demo.

Upvotes: 1

VBoka
VBoka

Reputation: 9083

You can do it with sub-query:

select group_id
from mailgroups 
where group_id in (select group_id from mailgroups where brand_id = 2)
GROUP BY group_id
HAVING COUNT(brand_id) = 1;

select group_id
from mailgroups 
where group_id in (select group_id from mailgroups where brand_id in (1,2))
GROUP BY group_id
HAVING COUNT(brand_id) = 2;

OR with join:

select a.group_id
from mailgroups a 
left join mailgroups b
on a.id = b.id
and b.brand_id in (1,2)
GROUP BY a.group_id
HAVING COUNT(a.brand_id) = 2;

select a.group_id
from mailgroups a 
left join mailgroups b
on a.id = b.id
and b.brand_id in (2)
GROUP BY a.group_id
HAVING COUNT(a.brand_id) = 1;

Upvotes: 1

Related Questions