Question 21720 in Mysql Question posted in
The official MySQL documentation can be found here.

Return even if inner join returns no result – Mysql

Good day!

I have the following tables:

devices (id, device_name, device_uid, device_type, account_id)

device_data (id_device, timestamp, value1, value2, value3)

My query:

SELECT * 
FROM devices AS d 
INNER JOIN (SELECT * 
            FROM device_data 
            WHERE timestamp IN (SELECT MAX(timestamp) 
                                FROM device_data)) AS dd ON d.id = dd.id_device

With the above query I get all devices and their last entry from the device_data table. But if the device has no values in the device_data table yet, this is not captured by the query. But I expect an output like this:

id device_name device_uid device_type account_id id_device timestamp value1 value2 value3
1 device1 uid1 type1 1 1 some time some value some value saome value
2 device2 uid2 type1 1 NULL NULL NULL NULL NULL

So if there is no data in device_data for device2, the query should capture the device anyway and output the non-existing value from device_data as NULL.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In the meantime I have come to the following result. This query is much faster (0.0022s vs. 0.1945) than the one mentioned by @kergma.

    SELECT * FROM devices AS d 
LEFT JOIN (SELECT * FROM device_data d WHERE d.timestamp = (SELECT max(d2.timestamp) FROM device_data d2 WHERE d2.id_device=d.id_device))
AS dd ON d.id = dd.id_device;

  2. 0

    you need LEFT JOIN, to get all device records in output, with all device_data records, if any

    select * from devices d left join device_data dd on dd.id_device=d.id

    but also I see from your query, that you want to select only latest row from device_data for each device, so you should do it like this:

    select *
from devices d
left join device_data dd on dd.id_device_id=d.id
where not exists (select 1 from device_data where id_device=dd.id_device and 
timestamp>dd.timestamp)

    (you can place this "not exists" condition in ON clause as well)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search