MYSQL join 2 tables

Question

TABLE1

ID | NAME
1  | a   
2  | b  
3  | c  
4  | d

TABLE2

ID | TBL1_ID | NAME  
1  | 2       | x     
2  | 2       | y  
3  | 2       | z

I would like to join two tables to get all records from table 1 and all records joined from table 2 on ID

This query return only all rows from 1 table and 1 row from second table.

SELECT  a.*, COUNT(a.id) total FROM table1 a  
  LEFT JOIN table2 b ON a.id = b.tbl1_id    
  GROUP BY a.id  
  ORDER BY a.id DESC

Thanks.

Preet Sangha · Accepted Answer · 2010-10-06 08:46:40Z

1

SELECT  * total FROM table1 a  
  left JOIN table2 b ON a.id = b.tbl1_id

edited Oct 6, 2010 at 8:46

answered Oct 6, 2010 at 8:25

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Pękała · Accepted Answer · 2010-10-06 09:21:18Z

1

If You need to join both tables and show all fields on matching rows this should work:

SELECT  * FROM table1 a  
  LEFT JOIN table2 b ON(a.id = b.tbl1_id)

edited Oct 6, 2010 at 9:21

answered Oct 6, 2010 at 8:34

Michał Pękała

2,3895 gold badges23 silver badges32 bronze badges

4 Comments

a'r Over a year ago

You can't use USING here as the join column in the second table is tbl1_id.

miojamo Over a year ago

Would it join all rows from table2?

miojamo Over a year ago

Now I have joined all rows from table1 + first row from table2

Michał Pękała Over a year ago

@miojamo, It will take every row from table1 and stick it with every matching row (if exists) from table2, so in Your example the result will be: 1-a-null, 2-a-x, 2-a-y, 2-a-z, 3-c-null, 4-d-null

Collectives™ on Stack Overflow

MYSQL join 2 tables

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related