1. Tables used
# 1. goods
select * from goods;
+----------+--------+------------+----------+
| goods_id | cat_id | goods_name | owner_id |
+----------+--------+------------+----------+
| 1 | 1 | CDMA Phone | 1 |
| 2 | 1 | GSM Phone | 1 |
| 3 | 1 | 3G Phone | 2 |
| 4 | 3 | TP Phone | 1 |
+----------+--------+------------+----------+
# 2. cat
select * from cat;
+--------+---------------+
| cat_id | cat_name |
+--------+---------------+
| 1 | Mobile Phone |
| 2 | Settled Phone |
+--------+---------------+
# 3. owner
select * from owner;
+----------+------------+
| owner_id | owner_name |
+----------+------------+
| 1 | Davy |
| 2 | Caly |
| 3 | Jack |
| 4 | Rose |
+----------+------------+
2. How to join those three tables together?
#Possible attempt 1
select temp.*, owner.* from (select goods.*, cat.* from goods left join cat on goods.cat_id = cat.cat_id) as temp left join owner on temp.owner_id = owner.owner_id;
ERROR 1060 : Duplicate column name 'cat_id'
#Possible attempt 2
select temp.*, owner.* from (select goods.cat_id, goods.goods_name, goods.goods_id, cat.cat_name from goods left join cat on goods.cat_id = cat.cat_id) as temp left join owner on temp.owner_id = owner.owner_id;
ERROR 1054 : Unknown column 'temp.owner_id' in 'on clause'
#Possible attempt 3
select goods.cat_id, cat.cat_name, goods.goods_name, owner.owner_id, owner.owner_name from goods left join cat on goods.cat_id = cat.cat_id left join owner on goods.owner_id = owner.owner_id;
+--------+--------------+------------+----------+------------+
| cat_id | cat_name | goods_name | owner_id | owner_name |
+--------+--------------+------------+----------+------------+
| 1 | Mobile Phone | CDMA Phone | 1 | Davy |
| 1 | Mobile Phone | GSM Phone | 1 | Davy |
| 1 | Mobile Phone | 3G Phone | 2 | Caly |
| 3 | NULL | TP Phone | 1 | Davy |
+--------+--------------+------------+----------+------------+
#Bingo!