MySQL ONLY IN() equivalent clause

Question

I am giving a very abstract version of my question here, so please bear with me. I have a query that will check whether a particular body has certain multiple parameters of same type. Example, a boy has multiple selection as far as chocolates are concerned. But, I want to choose boys from the table who have exactly the chocolates I mention. Not more not less and not 'LIKE' or not 'IN()'.

SELECT boy_id from boys_chocolates WHERE chocolate_id ONLY IN('$string');

..where of course '$string' is a PHP variable containing comma separated values of only those chocolates I want to use to pull the boys.

I know this is invalid MySQL statement, but is there any valid equivalent to this?

EDIT:

This is more comprehensive query which gets records in special cases, but not always.

SELECT boys.* FROM schools_boys INNER JOIN boys ON boys.boy_id=schools_boys.boy_id
INNER JOIN chocolates_boys a ON a.boy_id=boys.boy_id INNER JOIN schools
ON schools.school_id=schools_boys.school_id WHERE a.chocolate_id IN(1000,1003)
AND 
            EXISTS
            (
                    SELECT 1
                    FROM chocolates_boys b
                    WHERE a.boy_id=b.boy_id
                    GROUP BY boy_id
                    HAVING COUNT(DISTINCT chocolate_id) = '2'
                    )

                GROUP BY schools_boys.boy_id HAVING COUNT(*) = '2'

Boys Table
+--------+-------------+
| id     | boy         |
+--------+-------------+
| 10007  | Boy1        |
| 10008  | Boy2        |
| 10009  | Boy3        |
+--------+-------------+

Chocolates Boys Table
+----+---------+--------------+
| id | chocolate_id | boy_id |
+----+--------------+---------+
| 1  | 1000         | 10007   |
| 2  | 1003         | 10007   |
| 3  | 1006         | 10007   |
| 4  | 1000         | 10009   |
| 5  | 1001         | 10009   |
| 6  | 1005         | 10009   |
+----+--------------+---------+

Nothing happens when I select 1000 alone to pull two boys (or) 1000 and 1003 to pull out the boy with ID 10007.

Subdue

Answer 1

this problem is called Relational Division

SELECT boy_id 
FROM   boys_chocolates 
WHERE  chocolate_id IN ('$string')
GROUP  BY boy_id 
HAVING COUNT(DISTINCT chocolate_id) = ? -- <<== number of chocolates specified

example:

SELECT boy_id 
FROM   boys_chocolates 
WHERE  chocolate_id IN (1,2,3,4)
GROUP  BY boy_id 
HAVING COUNT(DISTINCT chocolate_id) = 4

• SQL of Relational Division

however, if the chocolate_id is unique for every boy_id, DISTINCT keyword is optional.

SELECT boy_id 
FROM   boys_chocolates 
WHERE  chocolate_id IN (1,2,3,4)
GROUP  BY boy_id 
HAVING COUNT(*) = 4

---

UPDATE 1

...I want to choose boys from the table who have exactly the chocolates I mention. Not more not less...

SELECT boy_id 
FROM   boys_chocolates a
WHERE  chocolate_id IN (1,2,3,4) AND
        EXISTS 
        (
            SELECT  1
            FROM    boys_chocolates b
            WHERE   a.boy_ID = b.boy_ID
            GROUP   BY boy_id
            HAVING  COUNT(DISTINCT chocolate_id) = 4
        )
GROUP  BY boy_id
HAVING COUNT(*) = 4

• SQLFiddle Demo

Berke

+1 Great answer. I think you should take out the WHERE clause so that you don't return boys that have more than the items in the desired list. – Toxinantitoxin

Actually that wouldn't work either. I think you would have to need to add another HAVING clause to make sure it was just the desired 4. – Toxinantitoxin

The problem is that you will return any boys that have those 4, whether they have additional ones or not. You should remove the WHERE clause and add a second HAVING clause to avoid this. The second HAVING clause could be (in your example): AND COUNT(DISTINCT CASE WHEN chocolate_id IN (1,2,3,4) THEN chocolate_id ELSE NULL END) = 4. – Toxinantitoxin

@Toxinantitoxin good point, i misread this line: ...exactly the chocolates I mention. Forgive me, I will update the answer. – Berke

No problem. It was a great answer. Just missed that one point. – Toxinantitoxin

I cannot use your answer because I am joining 3 or more tables in the same query and the FROM clause is pointing to a 3rd table. Is there a way I don't have to bring any data from boys_chocolates table and certainly don't have to use 'FROM' clause on the same table? – Subdue

@Tom, I tried your additional HAVING cause, and it didn't work as expected. – Subdue

@Viz - Did you remove the WHERE clause first? Also you would need to the HAVING clause to the one JW suggested. It would look like this: HAVING COUNT(DISTINCT chocolate_id) = 4 AND COUNT(DISTINCT CASE WHEN chocolate_id IN (1,2,3,4) THEN chocolate_id ELSE NULL END) = 4. – Toxinantitoxin

If that doesn't work you need to post your table structures and/or a query joining the tables together so we have a better idea of what's going on. Obviously it is not as simple as the query you posted. – Toxinantitoxin

Yeah, it worked for one particular set of chocolates, but doesn't work in other sets. Weird. – Subdue

I have edited the question with a more detailed query that is a replica of the original query. – Subdue

can you give me sample records wherein the query faile to give correct result? – Berke

Little update, now after doing certain changes, I do not get any results for any collection. The query is exactly like the edited query. problem is that, the query I am writing is not exactly for boys and chocolates. :p – Subdue

See this question to understand what I mean by sample records with desired result The query I gave you works on my own set of data. Since it is not totally working with you, can you give me some set of your own data? – Berke

Please check the question again now. I have given the real records from my database. – Subdue

the query would be simplified by removeing the exists clause but it will violate this part ...But, I want to choose boys from the table who have exactly the chocolates I mention. Not more not less and not 'LIKE' or not 'IN()'. – Berke

That works in the link you gave me, unfortunately not on my computer which has exact query with few more tables joined. In fact if I select 1000 and 1003, the person with 1000 and 1005 is showing up. – Subdue

I want to add that when I change the count number manually, I get different results. One peculiar thing, when I just select 1000, and count is 1, I get only first boy, then I change count to 2 manually, I now get only second boy. So, The result is always 1 boy. – Subdue