SQL:确定一个1:多是否存在于另一个1:多中

Question

以航运业为例，这里有运输货物的公司(承运商)和运输货物的公司(发货人)。每个节点都有基于状态的路由。例如，发货人将货物从缅因州发送到加利福尼亚州，从缅因州发送到堪萨斯州，从新罕布夏州发送到阿拉巴马州，等等。承运商同样也有“路线”，这些路线由州到州定义。我想使用一个SQL查询，它可以为在任何发货目的地/目的地和任何承运商路线之间至少有一个匹配的发货人快速选择承运商。

数据库布局如下：

发货人与合同的关系是1:m。合同与车道有1:m的关系。车道以其最简单的术语originState和destinationState来定义。

从这里，我需要查询所有可能的运营商，看看哪些运营商可能会竞标该合同，这等同于该运营商至少支持一条与任何一条合同车道匹配的路线。图中载波表与其所有“路由”之间存在1:m的关系，同样定义为originState和destinationState。

此时，我只需要匹配的运营商列表。我甚至不关心哪条路线匹配，只关心他们是否有资格竞标合同。

谢谢!

应要求，以下是当前查询：

SELECT DISTINCT
    r.id as contractId, c.id as carrierId
FROM
    contract r
        JOIN
    contractLane rl ON rl.contractId = r.id
        JOIN
    lane l on l.origState = rl.originState and l.destState = rl.destState /*carrier lane matches any contract lane*/
        JOIN 
    carrier c on c.id = l.companyId
        JOIN 
    user u ON u.id = l.masterId and u.type = 4 /* lane owner is a carrier */
WHERE
    r.expireDate >= NOW()
        AND r.open = '1'
        AND r.active = '1'

Answer 1

SELECT S.id, S.originState, S.destinationState
FROM carrier AS C, shipper AS S
WHERE (C.originState = S.originState
    OR C.originState = S.destinationState
    OR C.destinationState = S.originState
    OR C.destinationState = S.destinationState)
    AND C.id = 34

34是您要过滤的运营商的id。

你也可以用JOIN来做同样的事情。

Answer 2

从你的描述来看，这是非常简单的。这里假设每个表的主键id为id，且外键列的格式为table_id。

以下是获得结果的一种方法：

 SELECT c.*
   FROM carrier c
   JOIN route r
     ON r.carrier_id = c.id
   JOIN lane l
     ON l.originState = r.originState
    AND l.destinationState = r.destinationState
   JOIN contract o
     ON o.id = l.contract_id
   JOIN shipper s
     ON s.id = o.shipper_id
  GROUP BY c.id

• 每个承运人都有零条、一条或多条航线。
• 每条航线都有一个起始和目的地状态。
• 每个发货人合同“航线”都有一个起始和目的地状态。
• 如果起始状态和目的地状态匹配，则路线将与“车道”匹配。
• “车道”引用单个合同。
• 合同引用单个发货人。

有可能存在多个匹配，因此我们使用承运商表的GROUP BY pk返回唯一的承运商列表。