Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.236.160]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB5LrNm06267 for ; Wed, 5 Dec 2001 16:53:25 -0500 (EST) (envelope-from roberto@brasileiro.net) Received: from roberto by brasileiro.net with local (Exim 3.33 #1 (Debian)) id 16Bjzv-0002Tq-00; Wed, 05 Dec 2001 14:54:35 -0700 Date: Wed, 5 Dec 2001 14:54:35 -0700 From: Roberto Mello To: Stephan Szabo Cc: pgsql-sql@postgresql.org Subject: Re: Getting matching and non-matching results (long) Message-ID: <20011205145435.A9169@cc.usu.edu> References: <20011205123703.A2725@cc.usu.edu> <20011205130527.W17641-100000@megazone23.bigpanda.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20011205130527.W17641-100000@megazone23.bigpanda.com> User-Agent: Mutt/1.3.23i X-Archive-Number: 200112/61 X-Sequence-Number: 5884 On Wed, Dec 05, 2001 at 01:07:20PM -0800, Stephan Szabo wrote: > > > > SELECT COUNT(incident_id), drug_name, grade_name > > FROM sds_offenders o, sds_drugs d, sds_drug_offenses do, sds_grades g > > WHERE o.drug_p = 't' > > AND o.offender_id = do.offender_id > > AND d.drug_id = do.drug_id > > GROUP BY drug_name, grade_name, d.sort_key > > ORDER BY d.sort_key > > I think you need a > g.gradeid=o.gradeid > in the where clause as well to constrain g to > the grade for which the offender belonged, right? Yes, I figured this mistake minutes after sending the message to the list. The problem is that with g.grade_id = o.grade_id there it gives me _only_ the grades that have incidents in them, instead of _all_ the grades with 0's for those without incidents. -Roberto -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer "Carrier detected." Go to the dentist...