user765368 user765368 - 3 months ago 6
MySQL Question

Get all rows of NOT value WITHOUT subquery

Let's say that I have a table like this

Id item_id type_id date
1 1 1 2016-08-11
2 2 2 2016-08-12
3 2 3 2016-08-13
4 3 4 2016-08-14
5 4 3 2016-08-15
6 2 3 2016-08-16


As you can see, I have item with id 2 that has 2 types (type_id 2 and 3). How do I get all items (item_id) that are NOT of type 3 WITHOUT using subqueries. For example the expected result should be:

Id item_id type_id date
1 1 1 2016-08-11
4 3 4 2016-08-14


If I write the following SQL statement:

SELECT * FROM my_table WHERE type_id <> 3 GROUP BY item_id;


The above statement returns also type 2 (because it has a row in the table that is ALSO not of type 3).

Any help please.

Answer

you can count the number of times an item_id has a type_id of 3 and only select those with a count of 0

SELECT  `Id`, `item_id`, `type_id`, `date`
FROM    MyTable
GROUP BY item_id
HAVING  COUNT(CASE WHEN type_id = 3 THEN 1
              END) = 0

SQL FIDDLE EXAMPLE

just be aware that if you have more than one record for each item_id, that grouping by item_id will only give you one of the records in your result.

Comments