pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Telling how many records are joined
2+ messages / 2 participants
[nested] [flat]

* Telling how many records are joined
@ 2002-01-04 16:29 Andrew Perrin <andrew_perrin@unc.edu>
  2002-01-04 19:20 ` Re: Telling how many records are joined Bill Cunningham <billc@ballydev.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Andrew Perrin @ 2002-01-04 16:29 UTC (permalink / raw)
  To: pgsql-sql

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:

SELECT 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;

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Telling how many records are joined
  2002-01-04 16:29 Telling how many records are joined Andrew Perrin <andrew_perrin@unc.edu>
@ 2002-01-04 19:20 ` Bill Cunningham <billc@ballydev.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Bill Cunningham @ 2002-01-04 19:20 UTC (permalink / raw)
  To: andrew_perrin@unc.edu; pgsql-sql

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
>





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-01-04 19:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-04 16:29 Telling how many records are joined Andrew Perrin <andrew_perrin@unc.edu>
2002-01-04 19:20 ` Bill Cunningham <billc@ballydev.com>

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