Using Left Join To Only Selection One Joined Row

I'm trying to LEFT JOIN two tables, to get a list of all rows from TABLE_1 and ONE related row from TABLE_2. I have tried LEFT JOIN and GROUP BY c_id, however I wan't the related r

Solution 1:

SELECT*FROM    table1 t1
LEFTJOIN
        table2
ON      o.id = 
        (
        SELECT  o_id
        FROM    table2 t2
        WHERE   t2.c_id = t1.c_id
        ORDERBY
                t2.c_id DESC, t2.isHeadOffice DESC, t2.o_id DESC
        LIMIT 1
        )

Create an index on table2 (c_id, isHeadOffice, o_id) for this to work fast.

The ORDER BY clause in the subquery may seem redundant but it is required for MySQL to pick the right index.

Solution 2:

This assumes that your isHeadOffice is a bit field and you will only have one head office per country.

SELECT
    Table1.c_id,
    Table1.Name,
    Table2.Office
FROM
    Table1
LEFTOUTERJOIN
    Table2
ON
    Table1.c_id = Table2.c_id
AND
    Table2.isHeadOffice =1

Solution 3:

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
ORDERBY3DESC