Compare two MySQL queries in one table-CodePudding

I would like to receive all users that does not contain the hobby reading. So basically query_2 should be subtracted with query_1 that I obtain all users without the reading value.

query_1: SELECT fk_user FROM table1 WHERE hobby = 'cooking';

query_2: SELECT DISTINCT fk_user FROM table1;

EDIT: The problem is that there are a lot more hobbys and it would be to much work to include them all. It is not possible to select all users and subtract the users that contain reading as a hobby?

id	hobby	fk_user
1	cooking	user_1
2	reading	user_1
3	cooking	user_2
4	reading	user_2
5	painting	user_3

CodePudding user response：

Unless I'm missing something all you need is:

SELECT DISTINCT fk_user FROM table1 WHERE hobby = 'cooking' AND hobby != 'reading' ;

Failing to see why you need a second query here.

CodePudding user response：

You can use aggregation to sum the occurrences of the hobby being 'reading' and filter on them being 0. Same goes analog for 'cooking'.

SELECT fk_user
       FROM table1
       GROUP BY fk_user
       HAVING sum(hobby = 'reading') = 0
              AND sum(hobby = 'cooking') > 0;

CodePudding user response：

There are many approaches to achive that. My proposition would be:

SELECT DISTINCT fk_user FROM table1 WHERE hobby IS NOT NULL AND hobby != 'reading' AND hobby = 'cooking';