CODEWITHSUNDEEP
mysql
Lucifix
Lucifix

Reputation: 21

MySQL - Check if array values with the same id exist

I've got table with two fields: id, numbers

I want to check if the same array values (etc 1,2,5,6) already exist in table, but they have to have the same 1nd row number (id).

I've tried with this sql query, but this one doesn't check if there is the same id:

SELECT id, numbers FROM `table` WHERE numbers IN (1,2,5,6) AND id = id

I know that "id = id" doesn't work, but I posted it so you'll know what I mean.

Upvotes: 0

Views: 1943

Answers (1)

Barmar
Barmar

Reputation: 780879

SELECT id, COUNT(*) num_count, GROUP_CONCAT(numbers ORDER BY numbers) all_numbers
FROM `table`
WHERE numbers IN (1, 2, 5, 6)
GROUP BY id

If you only want to see the ones that have all 4 numbers, add:

HAVING num_count = 4

If you want the IDs that have all and only those 4 numbers, use:

SELECT id, COUNT(*) all_count, SUM(numbers IN (1, 2, 5, 6)) in_count
FROM `table`
GROUP BY id
HAVING all_count = in_count

Upvotes: 1

Related Questions