MySQL联接:根据源表数据选择要从哪个表联接 [英] MySQL Joins: choosing which table to join from based on source table data
问题描述
我发现这个查询有点难以解释；我可能不熟悉某些简洁的措辞.
I find this query a bit tricky to explain; there's probably some concise wording I'm unfamilar with.
TL/DR:
我有一个events表.每个事件都与四个topics之一相关；原始数据只包含一个topic，因此是简单的一对一表关系.
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;
所以:
可能的主题(每个主题都有自己的表格):
Possible topics (each has their own table):
holidays | cruises | recruitment | fundays
示例:
id | holiday_name | other data....
------------------------------------------------
1 | basic holiday | ..etc..
2 | alpaca training | ..etc..
还有
id | funday_title | other data....
------------------------------------------------
1 | balloons! | ..etc..
2 | seaweed fun! | ..etc..
事件数据的主要来源是事件表；
The main source of the events data is the 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 | ....
因此，每个事件在该表中都有一个引用表(topic_type)和一个引用ID(reference_id).
So Each event has a reference table (topic_type) and a reference id (reference_id) in that table.
我现在想获得与每个事件相关的holidays/cruises/recruitment/fundays的标题.我有事件ID，因此SQL为:
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.
我希望输出将是对无法访问的表的引用；如果我使用CASE根据列条件选择要加入的表，那么我认为SELECT将始终引用3个无效的表引用，因此会引起问题.
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.
我希望输出为:
SELECT events.event_name, events.event_id, other_table.____ as topic_name ....
这样SQL结果可以是:
So that the SQL result can be:
Event_id = 1
Event_id = 1
event_id | event_name | topic_name
------------------------------------------------------------
1 | something | basic holiday
Event_id = 2
Event_id = 2
event_id | event_name | topic_name
------------------------------------------------------------
2 | some name | cruise Holiday title no.4
Event_id = 3
Event_id = 3
event_id | event_name | topic_name
------------------------------------------------------------
3 | horses! | balloons!
- 这可能吗?
- 这怎么办?
- MYSQL join tables based on column data and table name and
- How to use a case statement to determine which field to left join on and
- MySQL query where JOIN depends on CASE
我在这里看过
但是这些似乎要么是无法完成，要么是它们的位置是同一张桌子的不同列
But these all seem to be either that it can't be done or that their sitations are different columns from the same table
推荐答案
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 ...
这篇关于MySQL联接:根据源表数据选择要从哪个表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
