[Fixed]-MySQL merge data from multiple tables

by

0👍

✅

A solution that produces exactly the table of the question (combine JOIN and UNION, use NULL as X for missing columns)

SELECT parent_table.id as id,fk,url,books_for_sale,NULL as games_for_sale
FROM parent_table JOIN child_table_1 ON parent_table.id = child_table_1.fk
UNION
SELECT parent_table.id as id,fk,url,NULL as books_for_sale,games_for_sale
FROM parent_table JOIN child_table_2 ON parent_table.id = child_table_2.fk
ORDER BY id
👤Simon Hi

1👍

It seems you are trying to join tables using the primary key of each one of them, instead you should be using a foreign key, that way you can have a one to many relation.

The id column identifies each row for that specific table, you would need a websiteid column acting as a foreign key in the urls table, that way you can jon matching the foreign key to the primary key.

Select * from table1
Join table2 on table2.fkid = table1.id

another option that might work is union

SELECT * FROM parent_table
LEFT JOIN child_table_1
ON parent_table.id = child_table_1.fkid
UNION ALL
SELECT * FROM parent_table
LEFT JOIN child_table_2
ON parent_table.id = child_table_2.fkid

Also you must make sure that both select statements contain the same columns

👤ArturoAP

Leave a comment