Re: [pgsql-general] Need Help With a A Simple Query That's Not So Simple

Date: Mon, 31 Oct 2011 16:51:02 -0600
From: Bill Thoen <bthoen@gisnet.com>
To: Postgrresql <pgsql-general@postgresql.org>
Subject: Need Help With a A Simple Query That's Not So
Simple
Message-ID: <4EAF2656.6020303@gisnet.com>

I think this should be easy, but I can't seem to put the
SQL together correctly and would appreciate any help.
(I'm using Pg 8.4 in CentOS 5.5, if that matters.)

I have a table of Farms and a table of crops in a 1:M
relationship of Farms : Crops. There are lots of
different crops to choose form but for now I'm only
interested in two crops; corn and soybeans.

Some farms grow only corn and some grow only soybeans,
and some grow both. What I'd like to know is, which
Farms and how many are growing only corn, which and
how many are growing soybeans and which and how many are
growing both? I can easily get all the corn growers with:

SELECT a.*
FROM farms a
JOIN crops b
ON a.farm_id=b.farm_id
WHERE crop_cd='0041'

I can do the same with soybeans (crop_cd= '0081') and
then I could subtract the sum of these from the total
of all farms that grow either corn or soybeans to get
the number of farms growing both, but having to
do all those queries sounds very time consuming and
inefficient. Is there a better way to get the farm
counts or data by categories like farms growing only
corn, farms growing only soybeans, farms growing
both? I'm also interested in possibly expanding to a
general case where I could select more than two crops.
and get counts of the permutations.

Here's a sketch of the relevant pieces of the data base.

*Tables:*
farms crops
======= =======
farm_id bigint (pkey) crop_id (pkey)
type farm_id foreign key to farms
size crop_cd 0041 = corn 0081=soybeans
... year
...

Any help would be much appreciated.

TIA,

- Bill Thoen

I believe that what you are trying to do is called
relational algebra division. Take a look at these
references and see if either fits your needs:

http://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29

http://www.cs.arizona.edu/~mccann/research/divpresentation.pdf

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general