Are you trying to get a 1 or 0 depending upon whether or not there is an AQ_ record that matches? If so, then you could use a LEFT JOIN and an IF(ISNULL(AQ_.SITEID),0,1) or something like that.
I probably don't understand your query. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 05, 2007 3:40 PM > To: Jerry Schwartz; mysql@lists.mysql.com > Subject: RE: return integer for positive values > > > I am at a higher level than that. Here is my query: > SELECT distinct emt.group_name > FROM epa.aq_ambient_nad83 aq_, > epa.aq_ambient_pollutants pol, > epa.emtb_pollutant_group emt, > epa.pollutant pt > WHERE (aq_.siteid = pol.siteid) > AND (emt.group_code = pt.ambient_group_code) > AND (pol.paramcode = pt.paramcode) > AND AQ_.SITEID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" > value="#FORM.siteID#"> > > This gives me the list of pollutants at one site. If I > remove the "And aq.siteid...." part, then I get a list of all > the pollutants. > > --ja > > On Thu, 5 Apr 2007, Jerry Schwartz wrote: > > > I'm not sure I quite follow. Is there a concentration for > each location for > > each of a master list of pollutants? What concentration > would be considered > > "not there"? > > > > In any case, look at a construct like (ABS(concentration) > <= <tolerance>), > > where <tolerance can be either a constant or a field > associated with a > > particular pollutant. That construct will return TRUE or > FALSE, which > > evaluate to 1 and 0, respectively. > > > > I hope that helps. > > > > Regards, > > > > Jerry Schwartz > > Global Information Incorporated > > 195 Farmington Ave. > > Farmington, CT 06032 > > > > 860.674.8796 / FAX: 860.674.8341 > > > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, April 05, 2007 2:47 PM > > > To: mysql@lists.mysql.com > > > Subject: return integer for positive values > > > > > > > > > I have a table with a list of pollutants. I have a table of > > > locations, site names, counties, etc. I can join the these > > > tables together and get a list of of all the pollutants at a > > > site. But, what I am really wanting is a list of all the > > > pollutants with a integer field, zero for pollutant not here, > > > 1 for pollutant here. > > > > > > So that instead of the list I get now: > > > benzaldehyde > > > freon > > > formaldehyde > > > > > > I would get: > > > > > > lead 0 > > > acetone 0 > > > benzaldehyde 1 > > > butane 0 > > > freon 1 > > > formaldehyde 1 > > > > > > How can I return an integer for positive values like this > in a query? > > > > > > --ja > > > > > > > > > -- > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > -- > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]