SQL SELECT WHERE NOT IN from same table query

mysql, sql

Solution

Try to avoid correlated sub queries by using `LEFT JOIN`:

SELECT a.id, a.cpid, a.label, a.cpdatetime
FROM mytable AS a
LEFT JOIN mytable AS b ON a.label = b.label AND a.cpdatetime > b.cpdatetime
WHERE a.label LIKE 'CB%' AND a.cpid LIKE :cpid
  AND b.label IS NULL
GROUP BY a.label
ORDER BY a.cpdatetime ASC

Fiddle

If the join condition fails, the fields of the second table alias `b` will be set to `NULL`.

Alternatively, use a non-correlated sub query:

SELECT a.id, a.cpid, a.label, a.cpdatetime
FROM mytable AS a
INNER JOIN (
  SELECT label, MIN(cpdatetime) AS cpdatetime
  FROM mytable
  WHERE label LIKE 'CB%'
  GROUP BY label
) AS b ON a.label = b.label AND a.cpdatetime = b.cpdatetime
WHERE a.cpid LIKE '135%'
ORDER BY a.cpdatetime

First, you find the minimum `cpdatetime` for each label and then join that with the first table where you add the additional `cpid` condition.

Problem

I'm having problem with the following SQL query and MySQL ``` SELECT id, cpid, label, cpdatetime FROM mytable AS a WHERE id NOT IN ( SELECT id FROM mytable AS b WHERE a.label = b.label AND a.cpdatetime > b.cpdatetime ) AND label LIKE 'CB%' AND cpid LIKE :cpid GROUP BY label ORDER BY cpdatetime ASC ``` the table looks like this ``` 1 | 170.1 | CB55 | 2013-01-01 00:00:01 2 | 135.5 | CB55 | 2013-01-01 00:00:02 3 | 135.6 | CB59 | 2013-01-01 00:00:03 4 | 135.5 | CM43 | 2013-01-01 00:00:04 5 | 135.5 | CB46 | 2013-01-01 00:00:05 6 | 135.7 | CB46 | 2013-01-01 00:00:06 7 | 170.2 | CB46 | 2013-01-01 00:00:07 ``` I would like my query to return ``` 3 | 135.6 | CB59 5 | 135.5 | CB46 ``` Edit labels are dogs/cats and cpids are temporary family keeping the dogs/cats. Dogs/cats move from family to family. I need to find dogs/cats who were in :userinput family but only if they were not in another family previously I can't alter the database and just have to work with the data as they are and I'm not the one who wrote the application/database schema.

Original source