cfloop question

Report · Mar 18, 2009

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 PROJECT_DESCRIPT 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.

Report · Mar 18, 2009

Paul1 wrote:
> 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

Report · Mar 18, 2009

Ian Skinner wrote:
>
> 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.

Report · Mar 19, 2009

Hi Ian,

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

Thanks.

Paul.