CODEWITHSUNDEEP
phpmysqlsql
Victor Zhang
Victor Zhang

Reputation: 211

How to merge to MySQL tables based on id

Let's say I have three tables like these:

table name: 

id | name  
1  | Bob  
2  | Alice  
3  | Bryan  
etc

table pants: 

id | size  
1  | S  
3  | M

table skirt: 

id | size  
2  | M

How do I merge the three tables using MySQL and obtain a table like this:
result table: 

id | name | pants | skirt  
1  | Bob  |  S    |      
2  | Alice|       | M  
3  | Bryan|  M    |

When there's no matching id, the cell will just be blank.

Upvotes: 0

Views: 100

Answers (1)

Arion
Arion

Reputation: 31239

Join the tables with a left join. Like this:

SELECT
    tablename.id,
    tablename.name,
    tablepants.size AS pants,
    tableskirt.size as skirt
FROM
    tablename
    LEFT JOIN tablepants
        on tablename.id=tablepants.id
    LEFT JOIN tableskirt
        ON tablename.id=tableskirt.id

Upvotes: 4

Related Questions