cfloop question

[Paul1]

Hi All,

I have written a small application for students to select a school project
from a list of projects.

I have two tables:

PROJECT_DESCRIPT This has the code number for each of the projects and their
descriptions and the teacher running them.

PROJECT_RESULTS This has the results of the students selections. The name of
the student and the code number of the project taken from the PROJECT_DESCRIPT
table.


This has been fine unil this year I have been asked to show project
description and teacher in my results printout instead of just the code.


I thought that I could do it like this:


<cfquery name="output" datasource="biol_course">
select * from PROJECT_RESULTS
</cfquery>

<cfloop query = "output">

<cfoutput>


<cfquery name="output2" datasource="biol_course"">
select * from PROJECTS where code = #code#
</cfquery>

#name#, <cfoutput query="output2">#project#,
#teacher#</cfoutput></cfoutput><br />


</cfloop>

This clearly doesn't work so I am obviously looking at this wrongly and being
quite dumb!. Any advice on pointing me in the right direction would be
gratefully received. I don't do enough coldfusion work to develop my skills and
as a solo worker I don't have anyone else to share ideas.

Thanks,

Paul.

[Ian Skinner]

Post by Paul1
This clearly doesn't work so I am obviously looking at this wrongly and being
quite dumb!. Any advice on pointing me in the right direction would be
gratefully received.

Well the first mistake is to think this is a 'ColdFusion' problem. This
is a SQL problem and the solution will apply to any database technology.
What you want to be doing is a JOIN which is what makes modern
relational databases, well you know, relational.

I have heard good things about the books "Teach Yourself SQL in 10
minutes" and "Database Design for Mere Mortals". As well as there are
tons of good SQL tutorials all over the internet. A quick search for
SQL and JOIN should give you plenty of information.

But just to get you started as this is a very basic concept of
relational databases:

SELECT a.aField, a.bField, b.cField
FROM aTable a INNER JOIN bTable ON a.key = b.key

[Ian Skinner]

Post by Ian Skinner
SELECT a.aField, a.bField, b.cField
FROM aTable a INNER JOIN bTable ON a.key = b.key

FORGOT a character

SELECT a.aField, a.bField, b.cField
FROM aTable a INNER JOIN bTable *b* ON a.key = b.key

P.S. don't use the asterisks they are there just to highlight the
missing character.

[Paul1]

Hi Ian,

Thanks for the reply... I was being dumb... Thinking completley in the wrong direction.

Thanks.

Paul.