CODEWITHSUNDEEP
mysqlsql
novon
novon

Reputation: 993

Selecting latest record from multiple joined tables

I have a number of tables that look like this:

resource
---------
id
name

meta1
---------
id
resource_id
value
created_on

meta2
---------
id
resource_id
value
created_on

meta3
---------
id
resource_id
value
created_on

How can I get a result set containing the latest value from each of the meta tables for a given resource id using mysql?

I tried joining all the tables together on resource_id and selecting the max(created_on) and value columns from each meta tables. That would indeed give me the most recent created_on however the value is incorrect.

Upvotes: 3

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You can accomplish your result with a series of cross joins:

SELECT id, t1.value, t2.value, t3.value
FROM resource r
CROSS JOIN
(
    SELECT value
    FROM meta1 m1
    WHERE m1.resource_id = 10
    ORDER BY created_on DESC
    LIMIT 1
) t1
CROSS JOIN
(
    SELECT value
    FROM meta2 m2
    WHERE m2.resource_id = 10
    ORDER BY created_on DESC
    LIMIT 1
) t2
CROSS JOIN
(
    SELECT value
    FROM meta3 m3
    WHERE m3.resource_id = 10
    ORDER BY created_on DESC
    LIMIT 1
) t3
WHERE r.id = 10

I used the resource id 10 in the above query but you can replace it with whatever you want.

Upvotes: 2

Related Questions