Using LEFT JOIN to only selection one joined row
- by Alex
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 row from TABLE_2 to be 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
So what I am trying to get from this would be something like:
RESULTS
c_id Name Office
----------------------------
1 USA New York
2 Canada Toronto
3 England Manchester
4 France Paris
5 Spain NULL
I'm using PHP & MySQL. Any ideas?
