Your database should look as follows:
States----
StateID State
1 Arizona
2 Utah
Lakes-----
LakeID Lake
1 Powell
2 Huron
LakeStates-----
StateID LakeID
1 1
2 1
Now that the DB structure is ok, your query will look as follows:
SELECT S.State,L.Lake
FROM (LakeStates LS INNER JOIN States S ON LS.StateID = S.StateID) INNER
JOIN Lakes L ON LS.LakeID = L.LakeID
WHERE L.LakeID = 1
I hope that helps.
Jason
----- Original Message -----
From: "Victor Chou" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, May 07, 2001 2:28 PM
Subject: Re: having multiple groups
> 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
