> Date: Mon, 31 Oct 2011 16:51:02 -0600 > From: Bill Thoen <bthoen@xxxxxxxxxx> > To: Postgrresql <pgsql-general@xxxxxxxxxxxxxx> > Subject: Need Help With a A Simple Query That's Not So > Simple > Message-ID: <4EAF2656.6020303@xxxxxxxxxx> > > 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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
