How to conditionally select which table and column on a per row basis?

Question

In the returned results on a query to a 'Links' table I want to also add an extra column that shows a 'DisplayName' value that is selected from a JOIN conditional on the value of the 'toTable' per row. In addition sometimes that 'DisplayName' value is the result of a concatenation of columns.

My question is how do I do this?

I can write seperate queries and combine with UNION ALL though I wonder if there is a conditional way to do it

Each entry in the 'Links' table describes links between a pair of entries in two tables (of a possible 3 or more tables).

Links Table:

| LinkID | fromTable | fromID | toTable | toID |
|      1 |        A  |      1 |      B  |    1 |
|      2 |        A  |      1 |      C  |    2 |
|      3 |        B  |      1 |      C  |    1 |
|      4 |        C  |      1 |      A  |    1 |

Query results I want: WHERE Links.fromTable = 'A' OR Links.fromTable = 'C' for example

| LinkID | fromTable | fromID | toTable | toID | DisplayName      |
|      1 |        A  |      1 |      B  |    1 |  Some Title      |
|      2 |        A  |      1 |      C  |    2 |  Loud-Quack      |
|      4 |        C  |      1 |      A  |    1 |  Linus           |

3 Tables with different Columns:

Table A:    
| ID  | Name  |
| 1   | Linus |

-

Table B:    
| ID  | Title      |
|  1  | some title |

-

Table C:    
| ID  | CategoryA | CategoryB |
| 1   |   Bark    |    Softly |
| 2   |   Quack   |    Loud   |

- This is how I am doing this will UNION ALL which is not very flexible: EDIT: actually i think the below is wrong, i am looking at this now:

SELECT Links.*, A.Name AS DisplayName
FROM Links 
JOIN A ON Links.toID = A.ID 
WHERE Links.fromType IN ('A') AND Links.toType IN ('B')

UNION ALL

SELECT Links.*, CONCAT(C.CategoryB,'-',C.CategoryA) AS DisplayName
FROM Links 
JOIN C ON Links.toID = C.ID 
WHERE Links.fromType IN ('A') AND Links.toType IN ('C')

score 5 · Accepted Answer · answered Sep 03 '12 at 00:55

5

I thin you need inline CASE statement

SELECT  a.*,
        CASE toTable
            WHEN 'A' THEN b.Name
            WHEN 'B' THEN c.Title
            WHEN 'C' THEN CONCAT(d.CategoryB , '-',d.CategoryA )
        END AS DisplayName
FROM    `links` a
            LEFT JOIN tableA b
                ON a.toID = b.ID
            LEFT JOIN tableB c
                ON a.toID = c.ID
            LEFT JOIN tableC d
                ON a.toID = d.ID
WHERE   fromtable IN ('A', 'C')

SQLFiddle Demo

answered Sep 03 '12 at 00:55

John Woo

258,903
69
498
492

I have tested this with my actual use case, and it works a charm. – johowie Sep 03 '12 at 01:26
@johowie I used `left join` so if the `ID` doesn't exist on the three tables, it will still be present but has `NULL` value on `displayname` column. in order to format it neatly, you can use `COALESCE` for that. – John Woo Sep 03 '12 at 01:29
If a link is repeated will this return both links? In other words if fromType, fromID, toType, toID are identical in two rows of the Links table? In my actual links table there are other columns including a 'linkType' column. two items maybe linked twice with different linkTypes. – johowie Sep 03 '12 at 01:30
@johowie yes. but what do you want to do with them if they are repeated? ignore the other links or display all? – John Woo Sep 03 '12 at 01:32
I want to display them all, which is what is happening when i use your answer. great ! – johowie Sep 03 '12 at 01:37
upped for introducing me to sqlfiddle – tipu Jan 25 '14 at 01:21

lc. · Answer 2 · 2012-09-03T01:27:35.180

1

Well, with your example query, you could do this instead:

SELECT Links.*, COALESCE(A.Name,CONCAT(C.CategoryB,'-',C.CategoryA)) AS DisplayName
FROM Links 
LEFT OUTER JOIN A ON Links.toID = A.ID 
    AND Links.fromType IN ('A') AND Links.toType IN ('B')
LEFT OUTER JOIN C ON Links.toID = C.ID 
    AND Links.fromType IN ('A') AND Links.toType IN ('C')

This works as long as you will always get at most one match from the joined tables (that is, you don't have two different joins with the same Links.fromType IN ... and Links.toType IN ... criteria).

edited Sep 03 '12 at 01:27

answered Sep 03 '12 at 00:52

lc.

113,939
20
158
187

After thinking about it I think in my actual use case that repeated links are likely. otherwise this answer was a good option. I had to look up `COALESCE`: Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. [from http://dev.mysql.com/](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce) – johowie Sep 03 '12 at 01:25

How to conditionally select which table and column on a per row basis?

2 Answers2

SQLFiddle Demo