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

Thanks! Half the problem searching the 'Net for answers is knowing what it's called.

Regards,

Bill Thoen
GISnet
http://gisnet.com
303-786-9961

On Nov 1, 2011, at 10:01 AM, "James B. Byrne" <byrnejb(at)harte-lyne(dot)ca> wrote:

>
>> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>