Reputation: 747

MYSQL join 2 tables

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.

Upvotes: 0

Answers (2)

Reputation: 2389

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)

Upvotes: 1

Reputation: 65476

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

Upvotes: 1