RE: return integer for positive values
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]
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]
Re: return integer for positive values
[EMAIL PROTECTED] wrote on 04/05/2007 02:46:43 PM: 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 Simplyfying to these pseudo tables: Site (SiteId int not null auto_increment, SiteName varchar(100)) Pollutant (PollutantId int not null auto_increment, PollutantName varchar(100)) rlSitePollutant (SiteId, PollutantId primarykey(SiteId, PollutantId)) Then to list all sites, all pollutants and whether they exist at a site: Select PollutantName, Case when sp.PollutantId is Null then 0 else 1 END as ExistsAtSite, SiteName from Pollutant p left outer join SitePollutant sp on p.PollutantId=rlsp.PollutantId inner join Site s on rl.SiteId=s.SiteId The case statement can be very handy: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Hope that helps. Donna
RE: return integer for positive values
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]
