Postgres query problem, need to select unique values

Question

HI, I have the following query, but it's not quite doing what I expect it to do

SELECT DISTINCT(c.id) AS "CLIENT CODE",c.name AS "CLIENT NAME", count(cmp.id) as "NUMBER OF CAMPAIGNS ON LIVE AND PENDING" FROM clients AS c ,campaigns AS cmp WHERE cmp.clientid = c.id AND cmp.status NOT IN('S','C','X','?') GROUP BY c.name,c.id ORDER BY c.name;

What I want to achieve is the following. I have a clients table and a campaigns table in postgres. Clients have campaigns, so one client could have 100 campaigns. A client could have campaigns with status L,C, X or only L,C,P . There are only L,P,C,X,S,? fot the statusses. Now I want the query to return only clients who have campaigns with status L and P and not the others.

So in other words only clients with campaign status L and P should be return, if the client has a X , C and L and P, it should not be returned.

Hope this makes sence and is possible

If a client has only L or only P, should they be returned? — Quassnoi
– Quassnoi, Commented Nov 11, 2010 at 17:56
@Quassnoi - If a client has L and P then they should be returned, but if a client has L,P and C then they should not be returned. C,L,P are status values, status is a column in the campaigns table. — Elitmiar
– Elitmiar, Commented Nov 11, 2010 at 18:52
you did not answer my question. What if client has only L? — Quassnoi
– Quassnoi, Commented Nov 11, 2010 at 18:58
@Quassnoi, appologies, did not read it properly, then it shoul be returned — Elitmiar
– Elitmiar, Commented Nov 11, 2010 at 19:00

Quassnoi · Accepted Answer · 2010-11-11 19:03:52Z

4

SELECT  *
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')
        )

answered Nov 11, 2010 at 19:03

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres query problem, need to select unique values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related