Question 303620 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Return all results where all conditions (but no other) are true

There are two tables:

Packages

id     name
1      red
2      blue
3      yellow

Contents

packageid      item           size
1              square         A
1              circle         B
1              triangle       C
2              square         A
2              circle         B
3              square         A

If we query for { item:square, size:A } we only want { packages.id:3 }

If we query for { item:square, size:A } and { item:circle, size:B } we only want { packages.id:2 }

If we query for { item:square, size:A } and { item:circle, size:B } and { item:triangle, size:C } we only want { packages.id:1 }

If there is more than one package that matches exactly, we would like all of them.

This seems to work, but it’s not terribly elegant:

SELECT 
p.id,
p.name,
c.item,
c.size

FROM Packages p
LEFT JOIN Contents c ON (
  c.packageid=p.id
)

WHERE (
    ( p.id IN ( SELECT packageid FROM Contents WHERE item='square' AND size='A' ) 
    AND 
    ( p.id IN ( SELECT packageid FROM Contents WHERE item='circle' AND size='B' ) 
)
GROUP BY p.id 
HAVING ( SELECT COUNT(*) FROM Contents WHERE packageid=p.id ) = 2;
Tags:

2

Answers


  1. 0

    With EXISTS, you will get your wanted results

    SELECT 
    p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND  (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'triangle' AND size = 'C')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 3;
    id name item size
    1 red triangle C
    1 red circle B
    1 red square A 
    SELECT
p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 2;
    id name item size
    2 blue circle B
    2 blue square A

    fiddle

    Login or Signup to reply.
  2. 0

    We can achieve this using some CTEs :

    with filter as (
  select 'square' item, 'A' size union all
  select 'circle', 'B'
),
filter_with_count as (
  select *, count(*) over() as cnt
  from filter
),
cte as (
  select p.id, p.name, c.item, c.size,
         count(p.id) over(partition by p.id) as cnt
  from Packages p
  inner join Contents c on c.packageid = p.id
)
select c.id, c.name, c.item, c.size
from cte c
inner join filter_with_count f 
           on c.item = f.item 
           and c.size = f.size
           and c.cnt = f.cnt;

    Explanations :

    The first CTE, the condition(s) will be passed as a dataset.

    The second CTE count the rows using the window function COUNT().

    The third CTE was used to combine the two tables and count total rows per package.

    Finally, in order to obtain the desired result, we must join our filter rows with the combined data by item, size, and count.

    Which for this condition

    ('square', 'A') and ('circle', 'B')

    Results :

    id  name    item    size
2   blue    square  A
2   blue    circle  B

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search