Database Query Output - Suggestions Please!

[thomoose89]

Hi guys,

I'm a beginner at Coldfusion and I'd like your advice on what to do with an issue I have.

I currently have 3 related database queries that return results from 3 different tables in my database. I'd like the listed output from the second query to feature the name column of its specific row (these are 'eevent' in the query 'events' and 'Nname' in the Query 'artists'). Currently I'm only able to display the ID column from the second query. For example, I'd like the name from the 'artist' query rather than simply the 'artistID' from the 'applied' query.

I'm unsure of how to go about this and would greatly appreciate any suggestions anyone might have?

Here is my code:

<!--- THIS FINDS OUT THE PROMOTERS EVENTS --->

<cfquery name="events" datasource="071907cs07to">
SELECT eventID, eevent
FROM   Event
WHERE  epromoterID = #SESSION.auth.PromoterID#
</cfquery>

<!--- THEN THE APPLIED LIST FROM THOSE EVENTS --->

<cfquery name="applied" datasource="071907cs07to">
SELECT eventID, artistID
FROM   Applied
WHERE  eventID IN (#ValueList(events.eventID)#)
</cfquery>

<!--- THEN THE ARTIST NAMES FROM THOSE APPLICATIONS --->

<cfquery name="artists" datasource="071907cs07to">
SELECT Nname, artistID
FROM   Artist
WHERE  artistID IN (#ValueList(applied.artistID)#)
</cfquery>

<table class="main">
<cfoutput query="applied">

<tr>
<td>

'#artistID#'

would like to play:

#eventID#.
</td>

</tr>
</cfoutput>
</table>

Currently, the code is returning the ID numbers - I'd like to know if it's possible to output the names of the artists and events from the other two queries instead?

Thanks,

Tom

[tclaremont]

#Applied.ArtistID# will return the ArtistID content from the Applied query.

#Artists.ArtistID# will return the ArtistID content from the Artists query.

Get the idea?

[thomoose89]

That's not what I mean. The returned results have to be those from the second query. But I'd like these results to involve the related names rather than the ID numbers.

[tclaremont]

I see.

Do a quick websearch on queries and inner joins. This will allow you to JOIN the table that includes the actual name, which is linked to the number.

"Teach Yourself SQL in 30 minutes" is a great book, and a quick read. It will have you creating great queries in minutes.

[Dan_Bracuk]

Teach Yourself SQL in how many minutes?

[thomoose89]

@tclaremont - Thanks for your suggestion. I tried using JOIN and it's throwing up the following error:

"Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC  Socket][Microsoft][ODBC Microsoft Access Driver] Join expression not  supported."

I'm guessing it's not working because Access doesn't support inner join?

Have you any other possible solutions?

Thanks,

Tom

[Dan_Bracuk]

select somefields

from table1 t1, table2 t2

where t1.something = t2.something

[tclaremont]

LOL! I really meant 10 minutes. That was strictly a typo!

http://www.amazon.com/Sams-Teach-Yourself-SQL-Minutes/dp/0672325675/ref=sr_1_1?ie=UTF8&s=books&qid=1...

Different databases handle stuff like this slightly differently, hence the lack of specifics. I assure you that the book is well worth the 14 bucks or whatever it costs. The more you work with CF you more you will work with databases and queries.