<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18876">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Garamond>
<DIV><FONT face=Garamond>Greetings.</FONT></DIV>
<DIV><FONT face=Garamond>I am trying to make a query which builds a table that
has a 'present' or a NULL in each column of whether or not a
point is within that polygon. I have a polygon file of waterbodies and a
point file of invasive species data. I want to make a table that shows
which invasive species are known to be present in
the waterbody. So far I have come up with the following:
</FONT></DIV>
<DIV><FONT face=Garamond></FONT> </DIV>
<DIV><FONT face=Garamond>SELECT
DISTINCT<BR>wi_ogaa_harvest_waterbodies.mwbc,<BR>wi_ogaa_harvest_waterbodies.county,<BR>wi_ogaa_harvest_waterbodies.lake,<BR>(CASE
WHEN invspp_occurrence_data.tsn_char = '81339' then 'present' else NULL end) AS
"Zebra_Mussel", <BR>(CASE WHEN invspp_occurrence_data.tsn_char = '684624' then
'present' else NULL end) AS "Spiny_Waterflea",<BR>(CASE WHEN
invspp_occurrence_data.genus = 'Myriophyllum' then 'present' else NULL end) AS
"Eurasian_Water_Milfoil",<BR>(CASE WHEN invspp_occurrence_data.tsn_char =
'39007' then 'present' else NULL end) AS "Curly_Leaf_Pondweed",<BR>(CASE WHEN
invspp_occurrence_data.genus = 'Heterosporis' then 'present' else NULL end) AS
"Heterosporis"<BR>FROM inland.wi_ogaa_harvest_waterbodies<BR>JOIN
invasive_species.invspp_occurrence_data ON ST_Contains
(wi_ogaa_harvest_waterbodies.the_geom, invspp_occurrence_data.the_geom)<BR>ORDER
BY county, lake</FONT></DIV>
<DIV><FONT face=Garamond></FONT> </DIV>
<DIV><FONT face=Garamond>This seems to work correctly, but for each record
(lake) that has a species present a new line is formed. For
example, if a lake had all five species listed, the lake would be listed 6
times in the table (one line for each species present and one line for all
NULLs). If I use GROUP BY, it returns errors unless I include all columns
including "invspp_occurrence_data.tsn_char" and "invspp_occurrence_data.genus"
which puts me in the same spot I was without using GROUP BY. </FONT></DIV>
<DIV><FONT face=Garamond></FONT> </DIV>
<DIV><FONT face=Garamond>Does anyone know what I am doing wrong or how to
fix? Any help would be greatly appreciated.</FONT></DIV>
<DIV><FONT face=Garamond>Miigwech!</FONT></DIV>
<DIV><FONT face=Garamond>Dara</FONT></DIV></FONT></DIV></BODY></HTML>