CODEWITHSUNDEEP
mysqldjangopython-2.7
Johan Vergeer
Johan Vergeer

Reputation: 5578

MySQL merge data from multiple tables

I have a couple of tables, one table that contains the main content, and some tables that have been created with child classes.

This project is in MySQL

parent_table

===========================
| id       | name         |
---------------------------    
| 1        | website 1    |
| 2        | website 2    | 
| 3        | website 3    |
===========================

child_table_1 (for example cooking websites)

=========================================================
| id       | fk   | url             | books_for_sale    |
---------------------------------------------------------    
| 1        | 1    | http://foo.com  | Cooking food      |
| 2        | 3    | http://bar.com  | Cooding bars      |
=========================================================

child_table_2 (for example games websites)

========================================================
| id       | fk   |url             | games_for_sale    |
--------------------------------------------------   
| 1        | 2    |http://dad.com  | Daddy Daycare     |
========================================================

Now I need the information from all tables merged into one. 

========================================================================
| id       | fk   |url             | books_for_sale    | games_for_sale|
------------------------------------------------------------------------
| 1        | 1    |http://foo.com  | Cooking food      |               |
| 2        | 2    |http://dad.com  |                   | Daddy Daycare |
| 3        | 3    |http://bar.com  | Cooding bars      |               |  
========================================================================

I tried this:

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

And this:

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

But they did not work at all.
All I got back is the content from the last table in the join.

In the real case I have about a dozen tables with different columns that I need to work with.

I hope someone could help me out here. If someone know a nice way to do this in Django, that would be even better.

Thanks in advance.

Upvotes: 0

Views: 163

Answers (2)

Simon Hi
Simon Hi

Reputation: 3016

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

Upvotes: 0

ArturoAP
ArturoAP

Reputation: 432

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

Upvotes: 1

Related Questions