内部连接的sql子查询问题

Question

我的数据库中有这些表:

旅游 - 这是第一张 table

Tourist_ID - primary key
name...etc...

EXTRA_CHARGES

Extra_Charge_ID - primary key 
Extra_Charge_Description
Amount

Tourist_Extra_Charges

Tourist_Extra_Charge_ID
Extra_Charge_ID - foreign key
Tourist_ID - foreign key

所以这是例子

我有一个 Tourist_ID - 86 的游客。这位 ID 为 86 的游客需要额外付费，Extra_Charge_ID - 7 和 Extra_charge_ID - 11；

我尝试进行查询，以便我可以获取游客的姓名以及 EXTRA_CHARGES 表中的所有费用 没有 属于这个游客。

这是我尝试的查询 - 但它不返回任何内容。

SELECT
    Tourist.Name
    , EXTRA_CHARGES.Extra_Charge_Description
    , EXTRA_CHARGES.Amount 
FROM 
    Tourist 
    INNER JOIN TOURIST_EXTRA_CHARGES 
        ON Tourist.Tourist_ID = TOURIST_EXTRA_CHARGES.Tourist_ID 
    INNER JOIN EXTRA_CHARGES 
        ON TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID 
WHERE
    Tourist.Tourist_ID= 86 
    and EXTRA_CHARGES.Extra_Charge_ID NOT IN
    (   SELECT Extra_Charge_ID 
        FROM TOURIST_EXTRA_CHARGES te
        WHERE te.Tourist_ID = 86
    )

我当然只能通过此查询获得费用

SELECT * FROM EXTRA_CHARGES e
WHERE e.Extra_Charge_ID NOT IN
 (SELECT Extra_Charge_ID from TOURIST_EXTRA_CHARGES te

  WHERE te.Tourist_ID = 86
 )

但我找不到办法得到这个游客的名字

Answer 1

您可以使用两个选项，两者几乎相同，但 one may perform better than the other取决于您的 DBMS。

两者的原理是一样的，把游客和额外费用交叉连接起来，这样你就有所有游客的所有额外费用，然后使用NOT EXISTS或 LEFT JOIN/IS NULL消除游客的所有额外费用:

SELECT  Tourist.Name,
        EXTRA_CHARGES.Extra_Charge_Description,
        EXTRA_CHARGES.Amount 
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
WHERE   Tourist.Tourist_ID= 86 
AND     NOT EXISTS
        (   SELECT  1
            FROM    TOURIST_EXTRA_CHARGES
            WHERE   TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND     TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
        );


SELECT  Tourist.Name,
        EXTRA_CHARGES.Extra_Charge_Description,
        EXTRA_CHARGES.Amount 
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
        LEFT JOIN TOURIST_EXTRA_CHARGES
            ON TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
WHERE   Tourist.Tourist_ID = 86 
AND     TOURIST_EXTRA_CHARGES.Tourist_Extra_Charge_ID IS NULL;

编辑

由于您应用的两个条件在逻辑上是不同的，因此您需要使用两个查询来获取它。第一个和以前一样，游客没有的额外费用，第二个是所有额外费用的游客

SELECT  Tourist.Name,
        EXTRA_CHARGES.Extra_Charge_Description,
        EXTRA_CHARGES.Amount 
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
        LEFT JOIN TOURIST_EXTRA_CHARGES
            ON TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
WHERE   Tourist.Tourist_ID = 1 
AND     TOURIST_EXTRA_CHARGES.Tourist_Extra_Charge_ID IS NULL
UNION ALL
SELECT  Tourist.Name,
        NULL,
        NULL
FROM    Tourist
        CROSS JOIN EXTRA_CHARGES
        LEFT JOIN TOURIST_EXTRA_CHARGES
            ON TOURIST_EXTRA_CHARGES.Tourist_ID = Tourist.Tourist_ID
            AND TOURIST_EXTRA_CHARGES.Extra_Charge_ID = EXTRA_CHARGES.Extra_Charge_ID
WHERE   Tourist.Tourist_ID = 1 
GROUP BY Tourist.Name
HAVING COUNT(*) = COUNT(TOURIST_EXTRA_CHARGES.Tourist_Extra_Charge_ID);

Example on SQL Fiddle