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(at)gisnet(dot)com>
> To: Postgrresql <pgsql-general(at)postgresql(dot)org>
> Subject: Need Help With a A Simple Query That's Not So
> Simple
> Message-ID: <4EAF2656(dot)6020303(at)gisnet(dot)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(at)Harte-Lyne(dot)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