Just a simple union should do it:
select state1 as state, lake_id, lake from table
union
select state2 as state, lake_id, lake from table
union
select state3 as state, lake_id, lake from table
order by state
But shouldn't you be using a lake_id, state_id join table?
----- Original Message -----
From: "Gregg Kachel" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, May 08, 2001 9:12 AM
Subject: having multiple groups
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_01BA_01C0D708.2BE1EA70
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> I am trying to do a simple list of all states, then lakes in that state. =
> Problem is, since there are several lakes in two or three different =
> states, I can't seem to get them to be listed under each state.
>
> My table looks like this
> lake lake_id state1 state2 state3
> Powel 7 Utah Arizona
>
>
> my query is
> <cfquery name=3D"list" datasource=3D"houseboat">
> SELECT lake_id,lake,(statename, statename2,statename3) as statenames
> FROM rental_lakes
> order by statenames,lake
> </CFQUERY>
>
> and the output
> <CFOUTPUT QUERY =3D "list" Group=3D"statenames">
> <ul>
> <li>statenames#</li>
> <ul>
> =20
> <cfoutput ><li><a href=3D"lakes.cfm?lake_id=3D#lake_id#">#lake#</a> =
> </li>
> </cfoutput>
>
> Is this possible? Am I in the wrong direction here? What I need for =
> output is:
>
> Arizona
> *Lake Powell
>
> Utah
> *Lake Powell
>
> I have been working on this all week, trying differant things. If =
> someone could push me in the correct direction it would be greatly =
> appreciated.
> Thanks,
> Greggk
>
> ------=_NextPart_000_01BA_01C0D708.2BE1EA70
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; =
> charset=3Diso-8859-1">
> <META content=3D"MSHTML 5.50.4522.1800" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3DArial size=3D2>I am trying to do a simple list of all =
> states, then=20
> lakes in that state. Problem is, since there are several lakes in two or =
> three=20
> different states, I can't seem to get them to be listed under each=20
> state.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>My table looks like this</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>lake =
> lake_id =20
> state1 =20
> state2 state3</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>Powel =20
> 7 =
> =20
> Utah Arizona</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>my query is</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2><cfquery name=3D"list"=20
> datasource=3D"houseboat"><BR> SELECT lake_id,lake,(statename,=20
> statename2,statename3) as statenames<BR> FROM =
> rental_lakes<BR> order=20
> by statenames,lake<BR> </CFQUERY></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>and the output</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2> <CFOUTPUT QUERY =
> =3D "list"=20
> Group=3D"statenames"><BR> <ul><BR> &=
> lt;li>statenames#</li><BR> =20
> <ul><BR> <BR> <cfoutput=20
> ><li><a =
> href=3D"lakes.cfm?lake_id=3D#lake_id#">#lake#</a>=20
> </li><BR></cfoutput></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Is this possible? Am I in the wrong =
> direction here?=20
> What I need for output is:</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Arizona</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>*Lake Powell</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>Utah</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>*Lake Powell</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>I have been working on this all week, =
> trying=20
> differant things. If someone could push me in the correct direction it =
> would be=20
> greatly appreciated.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>Greggk</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_01BA_01C0D708.2BE1EA70--
>
>
> Archives: http://www.mail-archive.com/[email protected]/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
