CODEWITHSUNDEEP
mysqlsqlsearchselectcriteria
yves
yves

Reputation: 250

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

Upvotes: 3

Views: 2426

Answers (2)

contradictioned
contradictioned

Reputation: 1253

You could query with a self join for that:

SELECT DISTINCT package_id
FROM package_content a, package_content b
WHERE a.package_id = b.package_id
      AND a.content_number = 11 AND b.content_number = 22

Edit: For your second question: Just add that to the query. The package_content renamed to a is responsible for the content_number 11. Therefore you can ask, wether a has content_quality 1:

SELECT DISTINCT package_id
FROM package_content a, package_content b
WHERE a.package_id = b.package_id
      AND a.content_number = 11 AND b.content_number = 22
      AND a.content_quality = 1

Upvotes: 1

juergen d
juergen d

Reputation: 204766

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

Upvotes: 4

Related Questions