SQL Select-Join help

tfoster

I'm trying to retrieve a set of data from two related tables (control_id links the two).

table1:
control_id (auto inc)
description VARCHAR(25)
...

table2:
control_id int
memo text
timestamp timestamp

I'm trying to write an sql join to gather the memo field from the most recent entry (ONLY). In the case of:

SELECT * FROM table1 t1 LEFT JOIN table2 t2 USING (control_id) ORDER BY timestamp DESC

This can result in multiple entries for the same control_id (different dates). I want one entry for each control_id based on table1 with the most recent data in the memo field from table2 for the control_id.

Using a loop and two selects:
SELECT * FROM table1;
for (each row)
SELECT memo FROM table2 WHERE control_id=row->control_id ORDER BY timestamp DESC LIMIT 1

I'm using mySql, any suggestion are appreciated.

Anon

Hi,
I think you really close.
If you want to have only one unique control_id in your result so try to use
GROUP BY.

SELECT *
FROM table1 t1
LEFT JOIN table2 t2
USING (control_id)
group by control_id
ORDER BY timestamp DESC

Nice day to you :-)

tfoster

While this will return one record for each control_id, it seems to be ignoring the ORDER BY. Using GROUP still returns data from the first physical record it comes across in table2.

Any other idease why the descending index might not be working?

[deleted]

I don't have access to mysql to test now, but if you add "LIMIT 1" to your first query, isn't that working?

SELECT * FROM table1 t1 LEFT JOIN table2 t2 USING (control_id) ORDER BY timestamp DESC LIMIT 1

Anon

no... he doesn't want 1 per query, he wants 1 per control_id.

you could of course do the first query, get the multiple records for 1 control_id and let php sort them out....

or maybe something like this:
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.control_id = MAX(t2.control_id)

i'm not sure if that syntax is legal. if you were using any other database, you could just do a subselect there.

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.control_id = (SELECT control_id FROM table2 WHERE control_id=t1.control_id ORDER BY timestamp DESC LIMIT 1)

also, that is probably horidly wrong. mysql can't do subselects though