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.

John Woo · Accepted Answer · 2013-05-09T13:18:14.067

12

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

edited May 09 '13 at 13:18

answered May 09 '13 at 12:27

John Woo

258,903
69
498
492

+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. – Tom May 09 '13 at 12:36
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. – Tom May 09 '13 at 12:38
1

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`. – Tom May 09 '13 at 13:06
1

@Tom good point, i misread this line: `...exactly the chocolates I mention`. Forgive me, I will update the answer. – John Woo May 09 '13 at 13:08
No problem. It was a great answer. Just missed that one point. – Tom May 09 '13 at 13:14
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? – Vijay Kumar Kanta May 10 '13 at 14:32
@Tom, I tried your additional HAVING cause, and it didn't work as expected. – Vijay Kumar Kanta May 10 '13 at 14:40
@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`. – Tom May 10 '13 at 15:36
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. – Tom May 10 '13 at 15:38
Yeah, it worked for one particular set of chocolates, but doesn't work in other sets. Weird. – Vijay Kumar Kanta May 29 '13 at 13:14
I have edited the question with a more detailed query that is a replica of the original query. – Vijay Kumar Kanta Jun 01 '13 at 07:35
can you give me sample records wherein the query faile to give correct result? – John Woo Jun 01 '13 at 07:38
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 – Vijay Kumar Kanta Jun 01 '13 at 08:22
[See this question to understand what I mean by *sample records with desired result*](http://stackoverflow.com/questions/15656273/mysql-pivot-table-column-data-as-rows/15656384) 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? – John Woo Jun 01 '13 at 08:25
Please check the question again now. I have given the real records from my database. – Vijay Kumar Kanta Jun 01 '13 at 09:13
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()'.` – John Woo Jun 01 '13 at 09:18
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. – Vijay Kumar Kanta Jun 01 '13 at 09:34
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. – Vijay Kumar Kanta Jun 01 '13 at 11:12

MySQL ONLY IN() equivalent clause

1 Answers1

Linked