mysql - 查询未按预期返回结果

Question

我有以下查询

SELECT  id AS "CLIENT CODE",name AS "CLIENT NAME",(
                            SELECT
                                count(status)
                            FROM
                                campaigns
                            WHERE
                                clientid = clients.id
                            ) AS "TOTAL CAMPAIGNS"
FROM    clients
WHERE   id IN
        (
        SELECT  clientid
        FROM    campaigns
        WHERE   status IN ('L', 'P')
        )
        AND id NOT IN
        (
        SELECT  clientid
        FROM    campaigns
        WHERE   status NOT IN ('L', 'P')
        );

现在我有一个包含所有客户的客户表和一个包含状态列的事件表，该列包含 L、C、P、?、X 的值。现在，一个客户可以从事件中获得 1 行或多行。因此，换句话说，一个客户可以有多个事件，其中状态值分配给一个事件。 L,C,P,?,X 中的任一个

我希望查询返回给我的客户的事件状态是 L 和 P，不仅是 P，而且不仅是 L，还有 L 和 P。目前，查询返回的客户有 P 或 L或两者兼而有之。

Answer 1

这应该确保它同时拥有

WHERE   id IN 
        ( 
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('L') 
        ) 
    AND id  IN 
        ( 
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('P') 
        ) 

或者甚至尝试 Subqueries with EXISTS

WHERE   EXISTS
        (
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('L') 
        AND     clientid = client.id
        )    
    AND EXISTS
        (
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('P') 
        AND     clientid = client.id
        )