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