• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Multiple Join Question

Participant ,
Jun 18, 2008 Jun 18, 2008

Copy link to clipboard

Copied

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>
TOPICS
Advanced techniques

Views

511

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 18, 2008 Jun 18, 2008

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 18, 2008 Jun 18, 2008

Copy link to clipboard

Copied

How many records does your query return?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 19, 2008 Jun 19, 2008

Copy link to clipboard

Copied

My query should return 5 rows of data. Right now my results show nothing.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 19, 2008 Jun 19, 2008

Copy link to clipboard

Copied

quote:

Originally posted by: brianism
My query should return 5 rows of data. Right now my results show nothing.

The question was not, "What do your results show?". It was, "How many records did your query return?"

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Jun 19, 2008 Jun 19, 2008

Copy link to clipboard

Copied

First off, you have a really 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 20, 2008 Jun 20, 2008

Copy link to clipboard

Copied

INNER JOIN will only extract rows where the values being joined are common to each table. I think you need to see more about the join statements: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN. And just like paross1 said, you really need to change your data model.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 23, 2008 Jun 23, 2008

Copy link to clipboard

Copied

I'm not sure why the data model is bad. I have a table with "team" information then a table for "team challenge" information showing who will be competing against who. I am limiting the challenges to only 10 teams, that is why I used #team1#, #team2# etc. The problem is that inputs the #id# of the team from the "team" table for #team1#, #team2# etc. So I try to join the two tables to pull out the #teamname# instead of id numbers.

I have the query working pulling out the correct id numbers for team1, team2, etc. I just need to find a way to pull their teamname in from the "team" table according to their #id# that was put into the "teamchallenges" table under #team1#, #team2# etc.

Here is what I'm using:

<cfquery name="qcomp" datasource="mydb">
SELECT * FROM team a
INNER JOIN
teamchallenges b ON a.id = b.sentby
</cfquery>

<tr>
<td valign="top"><strong>Competition Name</strong></td>
<td valign="top"><strong>Teams Involved</strong></td>


</tr>
<cfoutput query="qcomp">
<tr>
<td valign="top">#compname#</td>

<td valign="top">
#team1#, #team2#, #team3#, #team4#, #team5#,

</td>
</tr>

This works in the following output:

Competition Name Teams Involved

Team Smooch and Co 9, 7, , , ,
The Thrilla in Manilla 7, 1, 5, , ,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Jun 23, 2008 Jun 23, 2008

Copy link to clipboard

Copied

quote:

I'm not sure why the data model is bad.
Well, you are seeing symptoms of why it is bad, that being the fact that it isn't easy to write a query with a simple join to get the team names. Lets say that instead of having seperate columns for each team in the teamchallenges table that you instead have an intermediate table named challenges2team where you had only 2 columns named teamID and challengeID. Then your teamchallenges table would only contain information directly related to challenges, such as id, compname, and your challenges2team would contain a row for every matching teamID and challengeID.

So, for example, lets say that you have challengeID = 1, and three teams associated with that challenge with teamID values of 1, 3, and 5. Your challenges2team table would contain 3 rows with challengeID = 1, one for each matching TeamID. In order to see what challenges were associated with which team, you could do this:

SELECT t.teamname,
c.id,
c.compname
FROM team t
INNER JOIN challenges2team ct ON t.id = ct.teamID
INNER JOIN teamchallenges c ON c.id = ct.challengeID
WHERE c.id = 1

Your output would be three rows for the three teams associated with that challenge.

.. and there you are.

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Jun 23, 2008 Jun 23, 2008

Copy link to clipboard

Copied

...or perhaps something like this:

SELECT id, compname, teamname
FROM (SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team1
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team2
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team3
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team4
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team5
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team6
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team7
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team8
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team9
UNION
SELECT c.id, c.compname, t.teamname
FROM teamchallenges c
INNER JOIN team t ON t.id = c.team10)
GROUP BY challenge_id, compname, teamname
ORDER BY challenge_id

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 23, 2008 Jun 23, 2008

Copy link to clipboard

Copied

LATEST
Phil,
That did exactly what I was trying to do. Thank you so very very much! I understand what you meant in the previous post and I will take that into consideration as well.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation