trying to do inner joins

[shawntbanks]

I am trying to have a list of artist, and beside them on the page a list of all artwork that is related to that artist. for example

artist 1:
artwork1
artwork2
artwork3
artwork4

This is my query.


<cfquery datasource="artworks" name="get_artist">
select artist.fname, artist.lname, artwork.name
from artist
inner join artwork on artist.artist_id = artwork.artist_id
group by artist_id
order by fname</cfquery>

This is where I am trying to make the table

<table><tr><td><div class="heading1">
Current Artists:</div></td></tr>
<cfoutput query="get_artist">
<tr><td valign="top">#fname# #lname#</td><td valign="top">#name#</td></tr>

</cfoutput>
</table>

I am probably missing the obvious.

Thanks in advance for your help

 

[r937]

you should not use GROUP BY in the query

instead, make sure you order by artwork within artist

<cfquery datasource="artworks" name="get_artist">
  select artist.fname
       , artist.lname
       , artwork.name
       , artist.artist_id
    from artist
  inner 
    join artwork 
      on artist.artist_id 
       = artwork.artist_id
  order 
      by artist.lname
       , artist.fname
       , artwork.name
</cfquery>

then in the CFOUTPUT, use the GROUP= parameter on the artist_id

note that the GROUP= parameter is on the artist_id, and not on the columns used in the ORDER BY

this is a tricky strategy but it works when each artist lname/fname combination is unique

if you have two artists with the same lname/fname it won't work correctly, you'll get a mishmash

<table><tr><td><div class="heading1">
  Current Artists:</div></td></tr>
<cfoutput query="get_artist" group="artist_id">
<cfset artistname="#fname# #lname#">
<cfoutput>
<tr><td valign="top">#artistname#</td>
<td valign="top">#name#</td></tr>
<cfset artistname="">
</cfoutput>
</cfoutput>
</table>

notice that there is a nested CFOUTPUT

the artist name requires a variable because you want it printed on the same row as the first artwork

the variable is reset after every row so that the 2nd and subsequent rows do not repeat the artist name

rudy

http://r937.com/

SQL Consulting

 

[shawntbanks]

Thanks r937 There stills seems to be a little problem. Here is the error that I am getting.

Error resolving parameter NAME


ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.

 

[r937]

the get_artist query does include the name column

can we see your cfoutput? are you referencing #name# outside the cfoutput?

rudy

http://r937.com/

SQL Consulting

 

[shawntbanks]

<table><tr><td><div class="heading1">
Current Artists:</div></td></tr>
<cfoutput query="get_artist" group="artist_id">
<cfset artistname="#fname# #lname#">
<cfoutput>
<tr><td valign="top">#artistname#</td>
<td valign="top">#name#</td></tr>
<cfset artistname="">
</cfoutput>
</cfoutput>
</table>

 

[r937]

i'm stumped

perhaps there's another (local) variable called name?

try specifying the query, i.e.

<td valign="top">#get_artist.name#</td></tr>


rudy

http://r937.com/

SQL Consulting

 

[philhege]

NAME is a reserved word and may be confusing the driver. Try renaming the field in the query.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?