Return even if inner join returns no result-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

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;