Received: from dc-mx05.cluster1.charter.net (dc-mx05.cluster0.hsacorp.net [209.225.8.15]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g04JK8m80227 for ; Fri, 4 Jan 2002 14:20:09 -0500 (EST) (envelope-from billc@ballydev.com) Received: from [24.205.204.5] (HELO ballydev.com) by dc-mx05.cluster1.charter.net (CommuniGate Pro SMTP 3.4.6) with ESMTP id 16724944; Fri, 04 Jan 2002 14:25:18 -0500 Message-ID: <3C36007E.7060705@ballydev.com> Date: Fri, 04 Jan 2002 11:20:30 -0800 From: Bill Cunningham User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:0.9.4) Gecko/20011128 Netscape6/6.2.1 X-Accept-Language: en-us MIME-Version: 1.0 To: andrew_perrin@unc.edu, pgsql-sql@postgresql.org Subject: Re: Telling how many records are joined References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200201/37 X-Sequence-Number: 6128 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 >