I have 2 tables:
both tables have data specific to a device_id and datetime, for example device color:
device_id, datetime, color
device_alerts:
device_id, datetime, alert
now there can be multiple records per device per day. So there can be multiple alerts and multiple colors for 1 device per day. And there are multiple devices.
I want to merge these 2 tables with a query. But when I do this, I get way to many rows. I don't know what I'm doing wrong. This is my query (it's sql lite because it is in redash)
select a.alarm_type, a.created_at, a.monitor_id, dc.datetime, dc.device_id, dc.color
from query_505127 as a
inner join query_505241 as dc on a.monitor_id = dc.device_id
inner join query_505241 as dc1 on dc.datetime = a.created_at_date
Without example data it's hard to know exactly what you want..
This version joins to query_505241 once, using both fields as JOIN criteria which I think is what you are after.
select a.alarm_type, a.created_at, a.monitor_id, dc.datetime, dc.device_id, dc.color
from query_505127 as a
inner join query_505241 as dc on a.monitor_id = dc.device_id
AND dc.datetime = a.created_at_date
This version is the same as your query but with the second JOIN being based on dc1.datetime rather than dc.datetime
select a.alarm_type, a.created_at, a.monitor_id, dc.datetime, dc.device_id, dc.color
from query_505127 as a
inner join query_505241 as dc on a.monitor_id = dc.device_id
inner join query_505241 as dc1 on dc1.datetime = a.created_at_date
Do either of these return what you'd expect?