Prev: empty rows in cfgrid
Next: ImageGetEXIFMetadata exception null null at line -1
From: brianism on 18 Jun 2008 16:38
Hello,
I am trying to pull information out of a database using joins. It doesn't seem
to be working. I get no results. Here is how my tables are set up and what I
am trying to join. I get no results whatsoever displayed, but no errors. I want
to show the "compname" and then the teams involved.

Table: team Fields: id, captain, teamname,
Table: teamchallenges Fields: id, compname, team1, team2, team3, team4, team5

The id in the table "team" is relational in the "team1, team2, team3, team4,
team5" fields of "teamchallenges" to determine more information to be pulled.
So here is my query:


<cfquery name="qcomp" datasource="mydb">
SELECT * FROM teamchallenges a
INNER JOIN
team b ON a.team1 = b.id
INNER JOIN
team c ON a.team2 = c.id
INNER JOIN
team d ON a.team3 = d.id
INNER JOIN
team e ON a.team4 = e.id
INNER JOIN
team f ON a.team5 = f.id
</cfquery>


And my output:

<cfoutput query="qcomp">
<tr>
<td valign="top">#compname#</td>
<td valign="top">
#teamname#,
<cfif team2 NEQ "">#teamname#,</cfif>
<cfif team3 NEQ "">#teamname#,</cfif>
<cfif team4 NEQ "">#teamname#,</cfif>
<cfif team5 NEQ "">#teamname#,</cfif>
<cfif team6 NEQ "">#teamname#,</cfif>
<cfif team7 NEQ "">#teamname#,</cfif>
<cfif team8 NEQ "">#teamname#,</cfif>
<cfif team9 NEQ "">#teamname#,</cfif>
<cfif team10 NEQ "">#teamname#,</cfif></td>
</tr>

</cfoutput>

From: WolfShade on 18 Jun 2008 16:51
I can never remember (because I rarely use them) which it is, but I think you
need to use LEFT INNER JOIN or RIGHT INNER JOIN because any columns that do not
have the data will prevent all associated items in other tables from displaying.

From: Dan Bracuk on 18 Jun 2008 19:57
How many records does your query return?
From: brianism on 19 Jun 2008 09:19
My query should return 5 rows of data. Right now my results show nothing.
From: paross1 on 19 Jun 2008 11:33
First off, you have a [b]really[/b] BAD data model. Team1, team2, team3.....
yuck. What happens if you need to add a team6, etc.? Also, joining them like
you did means that you will only return rows that contain values for ALL teams
at the same time. If you have any control over your data model, you should
migrate your teams out of challenges and into an associative entity, since I am
assuming that challenges and teams is a many to many relationship. Then you can
link all three tables easily in a query to display whatever attributes you need.

Phi

 |  Next  |  Last
Pages: 1 2 3
Prev: empty rows in cfgrid
Next: ImageGetEXIFMetadata exception null null at line -1