2
DUES_TABLE
ID       LNAME   FNAME   DUES    DATE
3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01

9450     Jones   Jane    12.50   2014-10-01
9450     Jones   Jane    12.50   2014-11-01
9450     Jones   Jane    12.50   2014-12-01

6074     Suggs   Tom     12.50   2014-10-01
6074     Suggs   Tom      0.00   2014-11-01
6074     Suggs   Tom     12.50   2014-12-01

I want to output all records, whether dues were paid or not, for those persons who have one or more zero dues entries, like so:

3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01
9450     Suggs   Tom     12.50   2014-10-01
9450     Suggs   Tom      0.00   2014-11-01
9450     Suggs   Tom     12.50   2014-12-01

Notice that Jane Jones is excluded because she paid all her dues. And Both Bob Smith and Tom Suggs have their zero and positive dues entries listed.

Anyone have an idea how I can achieve this result?

2
SELECT DISTINCT ID FROM tbl WHERE DUES = 0

will get the IDs of the naughty people, correct? So now you need to do a "self-JOIN" back to get the rest of the data:

SELECT DISTINCT a.*
    FROM tbl AS a
    JOIN tbl AS b  USING(ID)
    WHERE b.DUES = 0
    ORDER BY a.ID, a.DATE

Or...

SELECT *
    FROM tbl AS a
    WHERE EXISTS ( SELECT ID FROM tbl
                      WHERE DUES = 0
                      AND ID = a.ID
                 )
    ORDER BY ID, DATE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.