sql - 我可以在联接中引用后面的表吗？

我有如下代码：

SELECT *
FROM invoices
LEFT JOIN shipments ON (
    shipments.id = invoices.shipment_id
)
LEFT JOIN details ON (
    details.id = invoices.detail_id
)

基本上，我想通过invoices表中的标识符来查找shipments表和details表。不过，我还想使用如下方法将这两个表连接起来：

SELECT *
FROM shipments
JOIN details ON (shipments.order = details.order)

基本上，如果找到（invoices.shipping_id，invoices.detail_id）中的任何一个，我想要一种方法来拉取这两个表。但是，我不能在联接中引用后面的表，因此我不能只执行以下操作：

SELECT *
FROM invoices
LEFT JOIN shipments ON (
    shipments.id = invoices.shipment_id
    OR shipments.order = details.order
)
LEFT JOIN details ON (
    details.id = invoices.detail_id
)

有没有办法在拉两张桌子的同时做到这一点？
为了清晰起见，请编辑：
下面是一个示例表结构

CREATE TABLE invoices (
    id integer PRIMARY KEY,
    shipment_id integer,
    detail_id integer,
    data text
)

CREATE TABLE shipments (
    id integer PRIMARY KEY,
    data text
)

CREATE TABLE details (
    id integer PRIMARY KEY,
    shipment_id REFERENCES shipments (id),
    data text
)

invoices.shipment_id可能包含也可能不包含shipments.id，invoices.detail_id可能包含也可能不包含details.id。
如果invoices.shipment_id包含有效的id，我需要执行如下操作：

SELECT
    shipments.data as shipment_data,
    details.data as detail_data,
FROM invoices
JOIN shipments ON (invoices.shipment_id = shipments.id)
JOIN details ON (details.shipment_id = shipments.id)

如果invoices.detail_id包含有效的id，我需要：

SELECT
    shipments.data as shipment_data,
    details.data as detail_data,
FROM invoices
JOIN details ON (invoices.detail_id = details.id)
JOIN shipments ON (details.shipment_id = shipments.id)

有没有办法把这两者结合起来？

最佳答案

尝试

select * from invoices
left join (
  select shipments.id as ids, details.id as idd, * from shipments
  outer join details on (
   shipments.order=details.order
  )
) as sd on (
 sd.ids=invoices.shipment_id OR
 sd.idd = invoices.detail_id
)

关于sql - 我可以在联接中引用后面的表吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19162929/