MySQL Help with the correct join statement

I have 3 tables and I need to get all columns from them based on timeStamp, anyone can help with the correct MySql join statement ?

table1

id
timestamp
col_1
col_2

table2

id
timestamp
col_3
col_4

table3

id
timestamp
col_5
col_6

what i have is this statment < but because the last table is empty i have get wrong results

select * from table1
JOIN table2 ON table2.timestamp = table1.timestamp
JOIN table3 ON table3.timestamp = table1.timestamp
where table1.timestamp >"2020/28/2" and  table1.timestamp <"2020/28/3"

*note: table1 has a lot of rows

*note: table2 has a lot of rows

*note: table3 is empty

Charizard

5 years ago

Level 5

You need to use LEFT JOIN:

select * from table1
LEFT JOIN table2 ON table2.timestamp = table1.timestamp
LEFT JOIN table3 ON table3.timestamp = table1.timestamp
where table1.timestamp >"2020/28/2" and  table1.timestamp <"2020/28/3"

Left join allows to output records from the first table even if they don't match any records from the joined tables.

1 like

trin

5 years ago

Level 5

if u need all records as separate items, u need union

(
	SELECT id, 'table1' as source FROM table1 WHERE timestamp between '2020/28/2' and '2020/28/3'
)
UNION
(
	SELECT id, 'table2' as source FROM table2 WHERE timestamp between '2020/28/2' and '2020/28/3'
)
UNION
(
	SELECT id, 'table3' as source FROM table3 WHERE timestamp between '2020/28/2' and '2020/28/3'
)

1 like

Please or to participate in this conversation.