It's often so helpful to ask a question ;-)
No sooner than I posted the below, I *think* I found the solution.
SELECT ProjTypes.ProjType,Projects.ProjectTitle,ProjCats.ProjCat
FROM ProjTypes,ProjTypeProjects,Projects,ProjCats,ProjCatProjTypes
WHERE ProjCats.ProjCatID=ProjCatProjTypes.ProjCatID
AND ProjCatProjTypes.ProjTypeID=ProjTypeProjects.ProjTypeID
AND ProjTypes.ProjTypeID=ProjTypeProjects.ProjTypeID
AND ProjTypeProjects.ProjectID=Projects.ProjectID
Please let me know if not having coffee yet has destroyed my mind.
best, paul
At 06:01 AM 5/8/01 -0700, you wrote:
>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
