Monday, November 16, 2009

Mysql Joins - All records from left, One record from right table

t1 table


+----+--------+-------+
| id | name   | age   |
+-------------+-------+

|  1 |  kusal | 25    |
|  2 |  saman | 20    |
+----------+----+-----+



t2 table


+----------+----+------------+
| image_id | id | image_path |
+----------+----+------------+
|        1 |  1 | path1      |
|        2 |  1 | path2      |
|        3 |  1 | path 3     |
|        4 |  2 | path 1     |
|        5 |  1 | path 4     |
|        6 |  2 | path 5     |
+----------+----+------------+


SELECT * FROM t1 a left join t2 b on a.id = b.id group by b.id



+----+-------+-----+----------+------+------------+
| id | name  | age | image_id | id   | image_path |
+----+-------+-----+----------+------+------------+
|  1 | kusal |  25 |        1 |    1 | path1      |
|  2 | saman |  21 |        4 |    2 | path 1     |
+----+-------+-----+----------+------+------------+

Using group by for the right table id, it will only get the one result from right table.