Re: Which date/paper pairs are NOT represented?

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(at)gmx(dot)net