How to query multi 'or' and a and in SQL?

Question

a table like this

table

|primary_key|  project | tag |
|    1      |    2     |  3  |
|    2      |    2     |  0  |
|    3      |    2     |  4  |
|    4      |    2     |  5  |
|    5      |    3     |  0  |
|    6      |    2     |  0  |

I want to query project with tag ' (3 and 4) or (0 and 4) and (5 and 0)', In this example, the output should be project 2? how could I write this in a SQL? I tried using phpmyadmin to generate several result, but not work as I expect.

It's realy kind of your guys to help me. I change the question, if the condition is much more complex, can the query be from table a, table b, table c?

Answer 1

You want to do this with aggregation and a having clause:

select project
from t
group by project
having sum(tag = 0 or tag = 3) > 0 and
       sum(tag = 4) > 0;

Each sum() expression is counting the number of rows where the condition is true. So, the two conditions are saying "there is at least one row with tag = 0 or 3 and there is at least one row with tag = 4".

Answer 2

You can join the table to itself and check if every row with tag 3/0 has another row with tag 4.

SELECT DISTINCT a.project
FROM table a, table b
WHERE a.project= b.project AND
    ( a.tag = 3 AND b.tag = 4 ) OR 
    ( a.tag = 0 AND b.tag = 4 ) 

Updated according to the updated question.