I have three tables, Projects, Project Types (ProjTypes), and Project
Categories (ProjCats). The are 7 Project Categories and 37 Project
Types. Each Project Category may have several Project Types. Each Project
Type may have several Projects.
The table ProjTypeProjects relates IDs from ProjTypes and Projects
The table ProjCatProjTypes relates IDs from ProjCats and ProjTypes
The tables are listed below along with some sample data.
I couldn't get anywhere with the INNER JOIN syntax. The SQL below comes
close, but is not correct. I'd appreciate any help you could give me.
best, paul
Projects
ProjectID 1 2
ProjectTitle SPT1 SPT2
ProjTypes
ProjTypeID 1
ProjType blah
ProjCats
ProjCatID 1
ProjCat blah
ProjTypeProjects
ProjTypeID 1 6
ProjectID 1 2
ProjCatProjTypes
ProjCatID 1 1
ProjTypeID 1 6
SELECT ProjCats.ProjCat,ProjTypes.ProjType,Projects.ProjectTitle
FROM ProjCats,ProjTypes,ProjCatProjTypes,ProjTypeProjects,Projects
WHERE ProjCats.ProjCatID=ProjCatProjTypes.ProjCatID
AND ProjCatProjTypes.ProjTypeID=ProjTypes.ProjTypeID
AND ProjTypeProjects.ProjectID=Projects.ProjectID
At 02:36 PM 5/7/01 -0700, you wrote:
>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
