Mutiple criteria in 1 query

Question

I have one table of peoples names and I have another table of pledges these people have made.

They might pledge money against item 1,2,3 or 4.

Each time a pledge is made an entry is made in the pledge table with the id of the person and the pledge number they made.

I would like a query that gets a count of distinct people that made pledges for both 1 and 2 for example.

How can this be achieved?

Many thanks

Dave

Answer 1

You could try this using the EXISTS

SELECT     COUNT(DISTINCT PersonID) DistinctCountPersons
FROM       pledges p
WHERE     EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 1)
AND     EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 2)

EDIT

Regarding the second part of the query, you can try

SELECT  p.PersonID,
        pp.PersonName,
        SUM(p.Pledge) TotalPledged
FROM    pledges p INNER JOIN
        people pp ON p.PersonID = pp.PersonID
WHERE   EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 1)
AND     EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 2)
GROUP BY p.PersonID,pp.PersonName