CODEWITHSUNDEEP
mysqlsql
python
python

Reputation: 1940

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

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?

Upvotes: 3

Views: 117

Answers (4)

Praveen Lobo
Praveen Lobo

Reputation: 7187

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.

Upvotes: 2

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

Try this:

SELECT distinct
    a.project
from
    `table` a
        join
    `table` b ON b.project = a.project and b.tag = 4
where
    a.tag in (0 , 3)

SQL Fiddle Demo

Upvotes: 1

amaster
amaster

Reputation: 2163

This would be the query for the most recent edit (3 and 4) or (0 and 4) and (5 and 0) Not sure whether though it is ((3 and 4) or (0 and 4)) and (5 and 0) OR (3 and 4) or ((0 and 4) and (5 and 0)) So i chose the former.

SELECT
    project
FROM
    t
GROUP BY
    project
HAVING
    (sum(tag = 3 AND tag = 4) > 0 OR
    sum(tag = 0 AND tag = 4) > 0) AND
    sum(tag = 5 AND tag = 0)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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".

Upvotes: 4

Related Questions