<[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
