10 Replies Latest reply on Jun 23, 2008 10:43 AM by brianism

    Multiple Join Question

    brianism Level 1
      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>
        • 1. Re: Multiple Join Question
          WolfShade Level 4
          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.
          • 2. Re: Multiple Join Question
            Dan Bracuk Level 5
            How many records does your query return?
            • 3. Re: Multiple Join Question
              brianism Level 1
              My query should return 5 rows of data. Right now my results show nothing.
              • 4. Re: Multiple Join Question
                paross1 Level 2
                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
                • 5. Re: Multiple Join Question
                  Dan Bracuk Level 5
                  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?"
                  • 6. Multiple Join Question
                    dongzky Level 1
                    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.
                    • 7. Re: Multiple Join Question
                      brianism Level 1
                      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, , ,
                      • 8. Re: Multiple Join Question
                        paross1 Level 2
                        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
                        • 9. Multiple Join Question
                          paross1 Level 2
                          ...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
                          • 10. Re: Multiple Join Question
                            brianism Level 1
                            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.