CODEWITHSUNDEEP
mysqlexists
george kal
george kal

Reputation: 1

Mysql check combination through different rows

So I have the below table and one variable in php that has these values: 14,16

combo table

How can I check if the above combination of numbers exist in the table? The above combo exists with the id 3. I don't want to check the order of numbers (ex. "16,14" or "14,16")

I hope you will not be confused!

Upvotes: 0

Views: 210

Answers (1)

user8608099
user8608099

Reputation:

You just need GROUP BY with COUNT and HAVING:

SELECT id
FROM tablename
WHERE optid IN(14, 16)
GROUP BY id
HAVING COUNT(DISTINCT optid) = 2;
  • WHERE optid IN(14, 16) will ensure that id have those numbers.
  • COUNT DISTINCT will remove all id expect those that have exactly two optid.

demo

Results:

| id |
|----|
|  3 |
|  4 |

Note that: this query will give you those id that have at least 14 and 16. So it will give you both 3 and 4.

However if you want those that have exactly 14 and 16 and no more thing, then try this:

SELECT id
FROM tablename
WHERE optid IN(14, 16)
  AND id not in (SELECT id from tablename where optid not in(14,16))
GROUP BY id
HAVING COUNT(DISTINCT optid) = 2;

This will give you:

| id |
|----|
|  3 |

Demo

Upvotes: 2

Related Questions