Join tables together, creating new columns for foreign links

Question

Possible Duplicate:
Pivot Table With 3 tables

EDIT: Sorry, I got one of the columns wrong

I have the following tables:

_______________________________________
| ItemID | ItemName | ItemDescription |
|________|__________|_________________|
|   1    |  Alice   |       Foo       |
|________|__________|_________________|
|   2    |   Bob    |       Bar       |
|________|__________|_________________|

________________________
| LinkID | LinkAddress |
|________|_____________|
|   1    |  www.c.com  |
|________|_____________|
|   2    |  www.b.com  |
|________|_____________|
|   3    |  www.a.com  |
|________|_____________|

________________________________________
| AssocID | ItemID | LinkID | LinkType |
|_________|________|________|__________|
|    1    |   1    |   3    | Youtube  |
|_________|________|________|__________|
|    2    |   2    |   2    | Facebook |
|_________|________|________|__________|
|    3    |   2    |   1    | Twitter  |
|_________|________|________|__________|

I am trying to get a SQL Query to create a result like this:

__________________________________________________________________
| ItemName | ItemDescription | Youtube   | Facebook  |  Twitter  |
|__________|_________________|___________|___________|___________|
|  Alice   |       Foo       |   NULL    |   NULL    | www.a.com |
|__________|_________________|___________|___________|___________|
|   Bob    |       Bar       | www.c.com | www.b.com |   NULL    |
|__________|_________________|___________|___________|___________|

Is this possible?

John Woo · Accepted Answer · 2012-11-01T16:35:01.097

SELECT  a.ItemName, a.ItemDescription,
        MAX(CASE WHEN b.LinkType = 'Youtube' THEN c.LinkAddress ELSE NULL END) youtube,
        MAX(CASE WHEN b.LinkType = 'Facebook' THEN c.LinkAddress ELSE NULL END) Facebook,
        MAX(CASE WHEN b.LinkType = 'Twitter' THEN c.LinkAddress ELSE NULL END) Twitter
FROM    tableName a
        LEFT JOIN tableAssco b
            ON a.ItemID = b.ItemID
        LEFT JOIN tableLinks c
            ON b.LinkID = c.LinkID
GROUP BY a.ItemName, a.ItemDescription

SQLFiddle Demo

or by using Prepared Statements

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN b.LinkType = ''',
      LinkType,
      ''' then c.LinkAddress ELSE NULL end) AS ',
      LinkType
    )
  ) INTO @sql
FROM tableAssco;

SET @sql = CONCAT('SELECT  a.ItemName, a.ItemDescription, ', @sql, ' 
                   FROM    tableName a
                          LEFT JOIN tableAssco b
                              ON a.ItemID = b.ItemID
                          LEFT JOIN tableLinks c
                              ON b.LinkID = c.LinkID
                  GROUP BY a.ItemName, a.ItemDescription');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo

If I want to add a WHERE statement to it, such as `WHERE facebook NOT NULL`, how can I do that? I have tried it like that, but it says `facebook` is an unknown column — topherg, Nov 01 '12 at 16:33

Join tables together, creating new columns for foreign links

1 Answers1