CODEWITHSUNDEEP
mysqlconditional-statements
Bajlo
Bajlo

Reputation: 1427

Select rows that contain more than one condition simultaneously

I have table structure like this:

ID   cond
1    5
1    2
1    6
8    2
9    1
9    5

When I want to select rows that contain one or more conditions i use OR (...WHEN cond=2 OR cond=6 GROUP BY ID...)

But how to select rows that contain more than one condition simultaneously grouped by ID? E.g. when i want to find rows that contain cond 2 and 6, it returns only ID 1

Thanks

Upvotes: 1

Views: 2957

Answers (8)

mamadprog
mamadprog

Reputation: 29

This error for my code was because : Because the name I wrote for the condition "WHERE" was wrong, I should actually write the full name Instead of HUNGRY, I should write Hungry Owl All-Night Grocers because it was stored in the database under this name.

Here I wrote HUNGRY by mistake

SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) WHERE CustomerName = 'hassan' OR CustomerName = 'Hungry Owl All-Night Grocers' GROUP BY CustomerName HAVING COUNT(Orders.OrderID) > 5;

I am from Iran and I don't know English. I hope I have been able to convey my meaning well and have solved your problem or at least helped to understand your problem.

Upvotes: 0

vladr
vladr

Reputation: 66721

There are multiple ways of doing this.

Using COUNT (fastest):

SELECT id FROM tbl WHERE tbl.cond IN ( 2, 6 ) HAVING COUNT(DISTINCT cond) = 2 GROUP BY id

Using EXISTS (using nested loops, slower on very large tables, but less cryptical and more xtensible than the COUNT variant):

SELECT DISTINCT id FROM tbl AS tbl1 WHERE EXISTS (SELECT * FROM tbl AS tbl2 WHERE tbl1.id = tbl2.id AND tbl2.cond = 2) AND EXISTS (SELECT * FROM tbl AS tbl2 WHERE tbl1.id = tbl2.id AND tbl2.cond = 6)

Using GROUP_CONCAT (a MySql specific variation on the COUNT theme, but if you ever want exact matches, e.g. cond=2 and cond=6 an no other cond, then the below, altered to read SELECT id, GROUP_CONCAT(DISTINCT cond ORDER BY cond) AS conds ... WHERE conds='2,6' will perform best)

SELECT id, ','||GROUP_CONCAT(DISTINCT cond)||',' AS conds FROM tbl WHERE INSTR(conds, ',2,') > 0 AND INSTR(conds, ',6,') > 0 GROUP BY id

Upvotes: 2

Ralph Wiggum
Ralph Wiggum

Reputation: 699

Have you thought about using the UNION clause?

SELECT ID FROM MyTable WHERE cond = 2 UNION SELECT ID FROM MyTable WHERE cond = 6

It would also help if you could specify the exact output you are looking for. But from what I understood, UNION would be the way to go.

Upvotes: 0

Reputation:

This might be a little more flexable if there are no dups per id.

SELECT id
FROM tbl 
GROUP BY id
HAVING SUM(CASE cond 
    WHEN 2 THEN 1
    WHEN 6 THEN 1
    ELSE 0 END) > 1

Upvotes: 0

David M
David M

Reputation: 72930

You can use a join for this:

SELECT  ID
FROM    tbl t1
        INNER JOIN
                tbl t2
                ON t1.ID = t2.ID
WHERE   t1.cond = 2
AND     t2.cond = 6

Or this, if ID/cond pairs are unique:

SELECT  ID
FROM    tbl
WHERE   cond IN (2, 6)
GROUP BY ID
HAVING COUNT(cond) = 2

Upvotes: 3

casperOne
casperOne

Reputation: 74560

You could use subqueries and a group to do this. Assuming you know the number of values you need to find, you can do this:

select
    t.ID
from
    (
        select distinct
            ID, cond
        from
            tbl
        where
            tbl.cond in (2, 6)
    ) as t
group by
    t.ID
having
    count(t.cond) = 2

For the general case, you would simply have to update the list of conditions that must exist (i.e. "(2, 6)") to include the new values, and then update the having clause (i.e. "having count(t.cond) = 2") to equal the total number of values.

Upvotes: 1

JohnFx
JohnFx

Reputation: 34917

Here's another syntax that is a little more transparent (although not as performant) about what it is doing.

SELECT DISTINCT ID 
FROM MyTable T
WHERE 
  EXISTS(SELECT ID FROM MyTable T1 WHERE Cond=2 AND T1.ID=T.ID) AND
  EXISTS(SELECT ID From MyTable T2 WHERE Cond=6 AND T2.ID=T.ID)

Upvotes: 0

Andrew Rollings
Andrew Rollings

Reputation: 14571

SELECT DISTINCT(ID) WHERE (cond = 2) OR (cond = 6)

Upvotes: 0

Related Questions