CODEWITHSUNDEEP
mysqlgroupingwhere-clause
noctua
noctua

Reputation: 135

MySQL, filter in GROUP BY

I am working on a MySQl database and have something like a tracking system for our invetory. With the inventoryNumber of the device, the person who got it, when he got it and when he returned it. If the returned column is null, then the person still has the device. 

|InventoryNumber | user  | receive    | returned   |
+----------------+-------+------------+------------+
| X1             | Mark  | 2017-01-01 | 2017-01-03 |
| X1             | Mark  | 2017-01-03 | null       |
| X2             | Frank | 2017-01-01 | null       |

now I want to know which devices are not in use. So first I have to GROUP BY InventoryNumber but "show" the null. Afterwords I have to filter WHERE returned IS NOT NULL

I know how to do that seperately, but after the GROUP BY he takes always the first row and not the one with the null, so I can't use WHERE.

I hope you understand my problem and can help me.

Upvotes: 2

Views: 1080

Answers (3)

Лев Макаренко
Лев Макаренко

Reputation: 334

You can try this:

select
`InventoryNumber`
from
`track`
where
`returned` is NOT NULL
group by `InventoryNumber`;

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use sub query with NOT IN, e.g.:

SELECT * 
FROM table
WHERE returned IS NOT NULL
AND InventoryNumber NOT IN (
   SELECT InventoryNumber 
   FROM table
   WHERE returned IS NULL
);

Upvotes: 1

Mihai
Mihai

Reputation: 26784

This will return all numbers where there is not a single row which is NULL. A quick reminder: WHERE filters rows HAVING filters groups.

SELECT InventoryNumber 
GROUP BY InventoryNumber 
HAVING SUM(returned IS NULL)=0

Upvotes: 2

Related Questions