Query not returning results as expected

I have the following query

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')
      开发者_运维百科  );

Now I have a clients table with all clients and a campaigns table with a status column that contains values of either L,C,P,?,X. Now one client can have 1 or more rows from campaigns. So with other words a client can have more than one campaigns with status values assigned to a campaign. Of either L,C,P,?,X

What I want the query to return to me is clients with campaigns where the status is L and P and not only P and not only L but both L and P. At the moment the query returns client that either have P or L or both.

This should ensure that it has both

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

Or even try 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
        ) 

I would re-write your query the following way:

SELECT id AS "CLIENT CODE",name AS

"CLIENT NAME",(

                        SELECT
                            count(*)             //count all
                        FROM
                            campaigns
                        WHERE
                            clientid = clients.id

) AS "TOTAL CAMPAIGNS" FROM clients

WHERE id IN

( SELECT clientid

    FROM    campaigns

    WHERE   status='L'

    )

    AND id 

    (

    SELECT  clientid

    FROM    campaigns

    WHERE   status='P'


    );

What I am not sure is about the final variable called total campaigns, what do you wanna put there the total amount of campaigns? if so it is correct what I wrote, but if your idea is another please re-explain it to me and I will answer again.