如何使用OR为多个列执行左连接

My question is very basic as it deals with two tables: reservations and events.

我的问题非常基本，因为它涉及两个表：预订和事件。

Table reservations contains 5 column which indicate the event type, this is a number.

表保留包含5列，表示事件类型，这是一个数字。

For example:

例如：

table reservations

event_1  event_2  event_3  event_4  event_5
181      177      485      772      474

Table events contain the details about the event.

表事件包含有关事件的详细信息。

For example:

例如：

table events

id     event_key    location
181    8888512      10
177    2255998      88
485    7895201      12
772    1212855      22
474    2125495      10

I would like to be able to write a query to which I will provide an event_key, and using the id from the table events, get to the reservations table.

我希望能够编写一个查询，我将提供一个event_key，并使用表事件中的id，进入预订表。

I have a limited knowledge of using joins, or sub-queries, I can get the idea with some explanation and help but I am unsure as to what method will get me the results I need. I hope that through this question I can get a bit of good help.

我对使用连接或子查询知之甚少，我可以通过一些解释和帮助获得这个想法，但我不确定哪种方法可以获得我需要的结果。我希望通过这个问题，我可以得到一些好的帮助。

Thank you.

谢谢。

UPDATE

UPDATE

This is the type of query I was thinking of:

这是我想到的查询类型：

SELECT 
    e.[id],
    e.[event_key],
    e.[event_location],
    e.[event_room],
    e.[event_description],
    e.[event_instructor], 
    r.[reservation_id], 
    r.[guest_first_name], 
    r.[guest_last_name], 
    r.[guest_age], 
    r.[guest_city], 
    et-cetera...
FROM events e 
LEFT JOIN reservations r
ON e.id IN (r.event_1, r.event_2, r.event_3, r.event_4, r.event_5)
WHERE e.event_key IN ('8888512', '7895201', '2125495')

1 个解决方案

#1

If you want to determine if event is event_1, event_2 and so on, you need to UNPIVOT the reservation table and JOIN it with the event table:

如果要确定事件是否为event_1，event_2等，则需要对预留表进行UNPIVOT并将其与事件表连接：

DECLARE @event_key INT = 2125495;

WITH CteUnpivotReservations(event_id, event_type) AS(
    SELECT event_1, 'event_1' FROM reservations UNION ALL
    SELECT event_2, 'event_2' FROM reservations UNION ALL
    SELECT event_3, 'event_3' FROM reservations UNION ALL
    SELECT event_4, 'event_4' FROM reservations UNION ALL
    SELECT event_5, 'event_5' FROM reservations
)
SELECT
    r.*
FROM CteUnpivotReservations r
INNER JOIN event e
    ON r.event_id = e.id
WHERE
    e.event_key = @event_key

SQL Fiddle

SQL小提琴

#1