Using LEFT JOIN to select only one concatenated row

Question

I am trying to LEFT JOIN two tables to get a list of all rows from TABLE_1 and one related row from TABLE_2. I tried LEFT JOIN and GROUP BY c_id, however I do not want the related row from TABLE_2 sorted by isHeadOffice DESC.

Here are some sample tables.

TABLE 1 c_id Name ---------------- 1 USA 2 Canada 3 England 4 France 5 Spain TABLE2 o_id c_id Office isHeadOffice ------------------------------------------------ 1 1 New York 1 2 1 Washington 0 3 1 Boston 0 4 2 Toronto 0 5 3 London 0 6 3 Manchester 1 7 4 Paris 1 8 4 Lyon 0 

What I'm trying to get from this would be something like this:

 RESULTS c_id Name Office ---------------------------- 1 USA New York 2 Canada Toronto 3 England Manchester 4 France Paris 5 Spain NULL 

I use PHP and MySQL. Any ideas?

Answer 1

 SELECT * FROM table1 t1 LEFT JOIN table2 ON o.id = ( SELECT o_id FROM table2 t2 WHERE t2.c_id = t1.c_id ORDER BY t2.c_id DESC, t2.isHeadOffice DESC, t2.o_id DESC LIMIT 1 ) 

Create an index on table2 (c_id, isHeadOffice, o_id) so that it works quickly.

The ORDER BY in the subquery may seem redundant, but MySQL needs to select the correct index.

Answer 2

Why not:

 SELECT c_id, name, (SELECT t2.office FROM table2 t2 WHERE t2.c_id = t1.c_id AND t2.isHeadOffice = 1 LIMIT 1) office FROM table1 t1 ORDER BY 3 DESC 

Answer 3

This assumes your isHeadOffice is a bit and you will only have one head office for each country.

 SELECT Table1.c_id, Table1.Name, Table2.Office FROM Table1 LEFT OUTER JOIN Table2 ON Table1.c_id = Table2.c_id AND Table2.isHeadOffice = 1