10

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.

Improve this question
11
  • 3
    Don't apologise - this is a well written question compared to others where you have prise information out using thumbscrews. Commented Feb 22, 2013 at 6:17
  • Sorry, could you explain why it should not return row 1? Is :cpid = '13%' perhaps? Commented Feb 22, 2013 at 6:20
  • @Dave no errors query just return empty Commented Feb 22, 2013 at 6:20
  • @lc. row 1 should not be returned because cpid != '135%' AND a.cpdatetime > b.cpdatetime Commented Feb 22, 2013 at 6:23
  • Strange the query should return empty, it works on SQL Fiddle (aside from also returning row 2) Commented Feb 22, 2013 at 6:28

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I think this is really what you want to do - select the IDs that are the earliest IDs for each label, then select out of those the records with a 135 cpid and a CB label.

SELECT
  A.id, cpid, A.label, cpdatetime
FROM
  mytable AS a inner join
 (select id, label from mytable
  group by label
  having min(cpdatetime)) as b
on A.label=B.label and A.id=B.id
WHERE
  A.label LIKE 'CB%'
AND
  cpid LIKE '135%'
GROUP BY A.label
ORDER BY cpdatetime ASC;

http://sqlfiddle.com/#!2/ccccf/16

Improve this answer

4 Comments

Wouldn't having min(cpdatetime) always yield true?
both your solution and jack's work but I'm not sure which one I should mark as accepted. I understand jack's answer really easily compared to yours @joe so I'm not really sure, still trying to read on some more inputs and digging in MySQL documentation
Hm, I suppose it would, wouldn't it. MySQL is just letting me cheat with the group by and returning the lowest numbered row, isn't it, rather than meaningfully evaluating it... sigh
@Joe Here's a fiddle that proves it would give the wrong results by simply switching two rows in order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.