Search Postgresql Archives

Need Help With a A Simple Query That's Not So Simple

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux