From: Bill Cunningham <billc@ballydev.com>
To: andrew_perrin@unc.edu
To: pgsql-sql@postgresql.org
Subject: Re: Telling how many records are joined
Date: Fri, 04 Jan 2002 11:20:30 -0800
Message-ID: <3C36007E.7060705@ballydev.com> (raw)
References: <Pine.LNX.4.21L1.0201041123310.749-100000@nujoma.perrins>
It sounds like you want unique papercode items right?
Thats what distinct keyword is for:
SELECT distinct papercode, count(papercode) FROM papers, letters
WHERE papers.paperid=letters.paperid AND
letters.letterid IN (SELECT DISTINCT o_letterid FROM pattern_occurrences)
GROUP BY papercode;
Also a another trick you can include just those having a count of more
than one:
SELECT distinct papercode, count(papercode) FROM papers, letters
WHERE papers.paperid=letters.paperid AND
letters.letterid IN (SELECT DISTINCT o_letterid FROM pattern_occurrences)
GROUP BY papercode HAVING count(papercode) > 0;
- Bill
Andrew Perrin wrote:
>Greetings-
>
>I'm sure there's a (moderately) simple answer to this, but I'd love some
>help with it.
>
>I have a database with four tables:
>
>papers - information about newspapers, including a unique paperid
>letters - information and text of letters to the editors, including
> a unique paperid and the paperid in which it appeared
>patterns- word patterns occurring in one or more letters, including
> a unique patternid
>pattern_occurrences - a linking table containing a letterid, a patternid,
> and a count (the number of times the pattern occurs
> in the letter).
>
>The patterns and pattern_occurrences tables are quite large (around 3
>million records each).
>
>I'd like to know, for example, how many letters from each paper have one
>or more patterns already coded. The best I can do is:
>
>
>
>Thanks for any advice.
>
>----------------------------------------------------------------------
>Andrew J Perrin - andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
> Assistant Professor of Sociology, U of North Carolina, Chapel Hill
> 269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: billc@ballydev.com, andrew_perrin@unc.edu
Subject: Re: Telling how many records are joined
In-Reply-To: <3C36007E.7060705@ballydev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox