Search code examples
mysqlsqlsearchselectcriteria

SQL: how to select a single id that meets multiple criteria from multiple rows


On a MySQL database, I have the table below

package_content :

id | package_id | content_number | content_name | content_quality

1       99           11               Yellow           1
2       99           22               Red              5
3       101          11               Yellow           5
4       101          33               Green            5
5       101          44               Black            5
6       120          11               Yellow           5
7       120          55               White            5
8       135          66               Pink             5
9       135          99               Orange           5
10      135          11               Yellow           5

and i am looking a possibility to make search queries on it:

I would like to select the package_id where content_number could be 11 AND 22 (In this case it should select only package_id 99

I really don't know if it's possible in SQL since the statement AND will always results as false. If i use the statement OR i also get the package_id 99, 101, 120, 135 and that's not what i want.

Maybe my table is not well designed too, but any suggestions would help! Thanks in advance

Edit

I added the content_quality column

I used the sql query from juergen, works very well

select package_id
from package_content
where content_number in (11,22)
group by package_id
having count(distinct content_number) = 2

My last question is how could i now add another criteria : Select the package_id where content_number is 11 and 22 and content_number 11 has content_quality 1

Edit 2:

For the 2nd question i use now this query. Thanks to both of you who helped me! :)

SELECT *
FROM (
   SELECT package_id
   FROM package_content
   WHERE 
      (content_number=11 AND content_quality > 1)
      OR (content_number = 33 AND content_quality = 5)
      OR (content_number = 44 AND content_quality =5 AND content_name like 'Black')
   GROUP BY package_id
   HAVING count( DISTINCT content_number) = 3
   )t1
LEFT JOIN package_content ON package_content.package_id = t1.package_id

This will output

id | package_id | content_number | content_name | content_quality

3       101          11               Yellow           5
4       101          33               Green            5
5       101          44               Black            5

Solution

  • You need to group by the package_id and then use having to perform an aggregate function over the grouped data

    select package_id
    from package_content
    where content_number = 22
    or
    (
        content_number = 11 and content_quality = 1
    )
    group by package_id
    having count(distinct content_number) = 2