[SOLVED] List of tags and latest read times

[Kurrel]

Hi everyone,

 

I've created a report that displays a list of tags and their last read-time.

 

The list of tags is kept in table 'taglist' while the reads are in a table 'tagscans'.  The primary key, 'tagid' of 'taglist' is the foreign key of 'tagscans'.

 

The current way I'm doing it is to pull the list of tags from 'taglist' and then, inside a foreach loop, pulling the most recent scan with a second SQL query.

 

I am almost certain that this process can be reduced to a single joined SQL query, but my efforts so far have resulted in records in 'taglist' that do not have any related records in 'tagscans' not appearing.

 

I need all the records in 'taglist' and the single, most-recent record (if any) related in 'tagscans'.

 

My current SQL query, that drops the unmatched records,

SELECT * FROM taglist JOIN tagscans ON taglist.tagid=tagscans.tagid

 

EDIT : I originally started with a LEFT JOIN from taglist to tagscans, believing it would pull the full list therein regardless of tagscans contents... same problem as above.

 

I've tried searching the forum but my search-fu has not revealed a relevant thread, just lots of interesting reading.

 

The darn thing works the long-way around, but I'm sure it can work better this way!

[fenway]

Why not this?

 

SELECT t.tagid, MAX( readTimeField) 
FROM taglist AS t
LEFT JOIN tagscans AS s ON s.tagid=t.tagid
GROUP BY t.tagid

[Kurrel]

That got me on the right track!

 

My understanding was a little mistaken on the manner in which joins are done, but seeing a working version got me going.

 

This is what the sql query eventually ended up being :

 

SELECT tlist.tagid AS tagid, MAX( slist.date) AS date FROM taglist AS tlist LEFT JOIN tagscans AS slist ON slist.tagid=tlist.tagid GROUP BY tlist.tagid

 

The MAX function was also new to me.

 

Thank you very much!