用于检索并列结果的SQL

Question

我有一些表格排列成这样的sudo代码。

table event {
  id
  field_1
}

table event_link {
  id
  start_event_id
  end_event_id
}

我不想在一个查询中并排显示链接的“事件”，就像这样。

start_id、start_field_1、end_id、end_field_1

我真的很纠结于SQL。这就是我到目前为止所拥有的。

select start_event.id as start_id, start_event.field_1 as start_field_1, end_event.id as end_id, end_event.field_1 as end_field_1 from
 (
   select id, field_1 from event inner join
    (
      select start_event_id from event_link
    ) on event.id = event_link.start_event_id
 ) as start_event,
 (
   select id, field_1 from event inner join
    (
      select end_event_id from event_link
    ) on event.id = event_link.end_event_id
 ) as end_event;

这个查询对我不起作用，但它是我能得到的最接近的查询。我需要做什么才能得到我需要的结果？

Answer 1

使用两个连接：

select es.*, ee.*
from event_link el join
     event es
     on el.start_event_id = es.id join
     event ee
     on el.end_event_id = el.id