1

I find this query a bit tricky to explain; there's probably some concise wording I'm unfamilar with.

TL/DR:

How to set a SQL query to choose which table to JOIN depending on source table data?

I have a table of events. Each event relates to one of four topics; The original data was for only one topic so was a simple 1-to-1 table relationship.

But the client now wants to extend events to four different topics;

So:

Possible topics (each has their own table):

holidays | cruises | recruitment | fundays

Examples:

holidays table

    id     | holiday_name    | other data....
------------------------------------------------
    1      | basic holiday   | ..etc..
    2      | alpaca training | ..etc..

And

fundays table

    id     | funday_title    | other data....
------------------------------------------------
    1      | balloons!       | ..etc..
    2      | seaweed fun!    | ..etc..

The main source of the events data is the Events Table;

Events Table

event_id | reference_id | topic_type (ENUM) | event_name | other data.....
--------------------------------------------------------------
    1    |       1      |      hol          |  something |  ....
    2    |       4      |      cruise       |  some name |  ....
    3    |       1      |      funday       |   horses!  |  ....
    4    |       2      |      hol          |  whatever  |  ....

So Each event has a reference table (topic_type) and a reference id (reference_id) in that table.

I am now in a position where I want to get the title of the holidays / cruises / recruitment / fundays relating to each Event. I have the event Id so the SQL would be:

SELECT event_name, etc... FROM events WHERE event_id = 1

But I also want to retrieve the name of the topic in the same query;

I have tried something like this Q&A:

SELECT events.event_name, other_table.
FROM events 
CASE 
LEFT JOIN holidays other_table ON events.topic_type = 'hol' AND events.reference_id = other_table.id
WHERE events.event_id = 1

And here is where I get stuck; I don't know how to dynamically reference which table to join.

I expect the output will be references to unreachable tables; if I use CASE to select tables to JOIN based on the column criteria then I envisage the SELECT will always be referencing 3 table references that are invalid so will raise issues.

I would like the output to be:

SELECT events.event_name, events.event_id, other_table.____ as topic_name ....

So that the SQL result can be:

Event_id = 1

  event_id | event_name | topic_name
 ------------------------------------------------------------
      1    |  something | basic holiday

Event_id = 2

   event_id | event_name | topic_name
------------------------------------------------------------
       2    | some name  | cruise Holiday title no.4

Event_id = 3

  event_id | event_name | topic_name
 ------------------------------------------------------------
      3    |   horses!  |  balloons!
  • Is this possible?
  • How can this be done?

I have looked on here:

But these all seem to be either that it can't be done or that their sitations are different columns from the same table

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132

3 Answers3

1

You want multiple left joins, conditioned on the type. I think something like this:

SELECT e.*,
       COALESCE(h.name, f.name, c.name, r.name) as name
FROM events e LEFT JOIN
     holidays h
     on h.id = e.reference_id AND
        e.topic_type = 'holiday' LEFT JOIN
     fundays f
     on f.id = e.reference_id AND
        e.topic_type = 'funday' LEFT JOIN
     cruises c
     on c.id = e.reference_id AND
        e.topic_type = 'cruise' LEFT JOIN
     recruitment r
     on f.id = e.reference_id AND
        e.topic_type = 'recruitment'    
WHERE e.event_id = 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
SELECT m.field,
       COALESCE(s1.field, s2.field, s3.field, s4.field) AS field,
       ...
FROM main_table m
LEFT JOIN slave1_table s1 ON ...
LEFT JOIN slave2_table s2 ON ...
LEFT JOIN slave3_table s3 ON ...
LEFT JOIN slave4_table s4 ON ...
Akina
  • 39,301
  • 5
  • 14
  • 25
  • If this works I'm going to feel like a right idiot! – Martin Feb 22 '20 at 13:40
  • @Martin Do not forget, that all conditions by slave tables must be included in ON, not in WHERE. Or they must use the same COALESCE expression like in output list. – Akina Feb 22 '20 at 13:44
0

Consider also this approach: specify and join with a subquery, CTE or even view, that contains the union of the data from all the tables. For example:

SELECT e.*,
       t.name
FROM events e INNER JOIN
    (
      SELECT id, 'holiday' as type, name FROM Holidays
      UNION ALL
      SELECT id, 'funday' as type, name FROM Fundays
      UNION ALL
      SELECT id, 'cruise' as type, name FROM Cruises
      UNION ALL
      SELECT id, 'recruitment' as type, name FROM Recruitments
    ) t
  ON
      t.id = e.reference_id AND
      t.type = e.topic_type

I personally like to avoid the use of functions whenever I can, as they tend to have a negative effect on performance.

downernn
  • 176
  • 5