I have 3 tables.
and there are 2 other tables as
all the first 3 tables are connected to cameras on
camera_id foreign key. but the user’s table is connected through
requested_by with compares and archives but through
user_id with timelapses.
both the 3 main tables have a few similar columns.
I have written this query so far
SELECT ar.title, ar.exid, ar.status, ar.created_at, u.email requester_email, c.name as camera_name FROM archives ar LEFT JOIN users u ON ar.requested_by = u.id LEFT JOIN cameras c ON ar.camera_id = c.id UNION SELECT tl.title, tl.exid, tl.status, tl.inserted_at, u.email, c.name as camera_name FROM timelapses tl LEFT JOIN users u ON tl.user_id = u.id LEFT JOIN cameras c ON tl.camera_id = c.id UNION SELECT cm.name, cm.exid, cm.status, cm.inserted_at, u.email, c.name as camera_name FROM compares cm LEFT JOIN users u ON cm.requested_by = u.id LEFT JOIN cameras c ON cm.camera_id = c.id
my issue is:
I want to get all of the columns from every table in one query where It can also respect
where as well. such 2 tables have an inserted_at date and one has created_at, but all of 3 are datetime so one datetime can be used to filter all of the 3 tables data.
I don’t have much knowledge of databases but if what I am trying to do is achievable through some other way then please do guide me.