mysql - return one row from join table with same field name -

block :

------------- | id | name | ------------- | 1  | test| ------------- 

block relation coords

------------- | id | blockid| coordid ------------- | 1  | 1   | 1  ------------- | 1  | 1   | 2  -------------  | 1  | 1   | 3  ------------- 

block coords

    -------------     | id | name| type     -------------     | 1  | north   | n      -------------     | 2  | east   | e      -------------      | 3  | south   | s      ------------- 

now want join table , result in 1 row , it's not important title of result table , put last table in field

all block table have 3 relation coords table

    -------------     name| north | east | south     -------------     test | north | east | south 

try this:

select   b.name,   max(case when type = 'n' c.name end) north,   max(case when type = 'e' c.name end) east,   max(case when type = 's' c.name end) south block b inner join block_relation_coords r on b.id = r.blockid inner join block_coords          c on c.id = r.coordid group b.name; 

see in action here:

• sql fiddle demo

this give you:

| name | north | east | south | ------------------------------- | test | north | east | south |