MySQL query: All data from one table and second table only if it exists

Question

I have two tables in my database. The one has is a list (table b in the code example) of profiles. The second is a log table (table a in the code example) with records of when the profile was updated.

I want to execute a query that gives me every single profile on the site. Some profiles have not been updated so there is no record in the log table, I want them to be shown as well with a 0 timestamp.

The following code will return only the rows to which ids exist in both tables.

SELECT b.id, b.name, b.linkname, IFNULL(MAX(a.date),0) as lastupdate
FROM links a, list b
WHERE b.id=a.id

How can I get it to work, so I get a full list of profiles and the latest timestamp to which one exists and a 0 timestamp if it doesnt exist?

Thanks in advance. Hope this isnt trivial, I've been searching a ton, but couldnt find an example.

score 0 · Answer 1 · answered Feb 15 '13 at 11:54

Use LEFT JOIN to get data.. use GROUP BY to avoid repeated data.. because u have several entries in log table for a single profile..

SELECT b.id, b.name, b.linkname, IFNULL(MAX(a.date),0) as lastupdate
FROM list b
LEFT JOIN links a ON (a.id=b.id)
GROUP BY b.id;

MySQL query: All data from one table and second table only if it exists

1 Answers1