CODEWITHSUNDEEP
mysqlsql
fl0pp
fl0pp

Reputation: 33

MySQL join query with only one record from joined table, ordered by date

I have two tables in MySQL I want to join in one query.

Table one opc_tag:

id name

Table two opc_data:

id tag_id value created_at

I want to list all rows from opc_tag with the last record from opc_data based on the "created_at" field.

I've tried to find it on Google, but it does not work. The following query selected the tags with multiple records from opc_data-table.

SELECT t.*, d.*
FROM opc_tag t LEFT JOIN opc_data d ON t.id = d.tag_id
WHERE d.created_at = (
    SELECT MAX(d.created_at)
    FROM opc_data d2
    WHERE d2.tag_id = t.id
)

Upvotes: 2

Views: 980

Answers (2)

x4rf41
x4rf41

Reputation: 5337

i think this will do it:

SELECT t.*,d.* FROM opc_tag t 
LEFT JOIN opc_data d ON t.id = d.tag_id
WHERE d.created_at = (
    SELECT MAX(created_at) FROM opc_data WHERE tag_id = t.id
)
GROUP BY t.id

if you want to show all tags, even those that have no data, use this in the where clause:

WHERE d.created_at = (
    SELECT MAX(created_at) FROM opc_data WHERE tag_id = t.id
) OR d.created_at IS NULL

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

Try this it will get the last tag_id entered in the opc_data by ORDER BY created_at DESC LIMIT 1 and returns the data against tag_id from the opc_tag

SELECT *
FROM opc_tag 
WHERE id= (
    SELECT tag_id
    FROM opc_data 
   ORDER BY created_at DESC LIMIT 1 
)

Upvotes: 0

Related Questions