CODEWITHSUNDEEP
mysqlpivot
Silverboy.ir
Silverboy.ir

Reputation: 187

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 i want join this table and get result like this in just one row and also it's not too important the title of result table , just put from last table in this field

all the block table have 3 relation with coords table

    -------------
    name| NORTH | EAST | SOUTH
    -------------
    test | North | East | South

Upvotes: 0

Views: 106

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

SELECT
  b.name,
  MAX(CASE WHEN type = 'N' THEN c.name END) AS North,
  MAX(CASE WHEN type = 'E' THEN c.name END) AS EAST,
  MAX(CASE WHEN type = 'S' THEN c.name END) AS SOUTH
FROM block AS b
INNER JOIN block_relation_coords AS r ON b.id = r.blockid
INNER JOIN block_coords          AS c ON c.id = r.coordid
GROUP BY b.name;

See it in action here:

This will give you:

| NAME | NORTH | EAST | SOUTH |
-------------------------------
| test | North | East | South |

Upvotes: 2

Daniel
Daniel

Reputation: 1494

You need to create a Pivot Table... this page gives you a lot more info about it:

http://en.wikibooks.org/wiki/MySQL/Pivot_table

Hope it helps.

Upvotes: 1

Related Questions