Thread: Which date/paper pairs are NOT represented?

Okay, y'all have been very helpful so far so here's another difficult
query.

This is the same database I've asked about before:

papers: information about newspapers, including a unique paperid
letters: information and text of letters to the editor, including the
paperid

There are, as you might expect, numerous letters on a given date in a
given paper. What I need to know, though, is for what paper/date pairs in
my sample I do NOT have any letters (since this probably represents a
data-collection glitch).  I know I can do:

SELECT paper, date, count(date)  FROM papers, letters WHERE
papers.papercode = letters.papercode GROUP BY paper, date; 

but adding a HAVING count(date) < 1 seems to return 0 rows.

Thanks-
ap

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin@unc.edu - http://www.unc.edu/~aperrinAssistant Professor of Sociology, U of North
Carolina,Chapel Hill     269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA
 

Andrew Perrin writes:

> papers: information about newspapers, including a unique paperid
> letters: information and text of letters to the editor, including the
> paperid
>
> There are, as you might expect, numerous letters on a given date in a
> given paper. What I need to know, though, is for what paper/date pairs in
> my sample I do NOT have any letters (since this probably represents a
> data-collection glitch).  I know I can do:
>
> SELECT paper, date, count(date)  FROM papers, letters WHERE
> papers.papercode = letters.papercode GROUP BY paper, date;
>
> but adding a HAVING count(date) < 1 seems to return 0 rows.

That really doesn't work, because grouping only groups values that are
actually there, so asking for groups with a count of 0 isn't going to give
an answer.

Moreover, in your specific application you're looking for papers without
any associated letters, so doing an inner join on papers and letters is
exactly the opposite of what you want to do.

Here's a possibility:

SELECT paper, date FROM papers WHERE papercode NOT IN (SELECT papercode
FROM letters);

-- 
Peter Eisentraut   peter_e@gmx.net