1 2 Previous Next 42 Replies Latest reply on Jun 4, 2006 9:39 AM by paross1

    2 where clauses

    richy2424 Level 1
      Hi i have 2 tables

      **TABLE1**
      PlayerName
      PlayerID
      Points
      BonusMom
      BonusTwt
      BonusTrs

      **TABLE2**
      TeamName
      QONE
      QTWO
      QTHREE
      PlayerOne
      PlayerTwo
      PlayerThree
      PlayerFour
      PlayerFive
      PlayerSix
      PlayerSeven
      PlayerEight
      PlayerNine
      PlayerTen
      PlayerEleven

      What i need is
      Total of (BonusMom) where PlayerID = QONE
      Total of (BonusTwt) where PlayerID = QTWO
      Total of (BonusTrs) where PlayerID = QTHREE

      Total of (Points) where
      PlayerID = PlayerOne or
      PlayerID = PlayerTwo or
      PlayerID = PlayerThree or
      PlayerID = PlayerFour or
      PlayerID = PlayerFive or
      PlayerID = PlayerSix or
      PlayerID = PlayerSeven or
      PlayerID = PlayerEight or
      PlayerID = PlayerNine or
      PlayerID = PlayerTen or
      PlayerID = PlayerEleven


      Now i have been playing around with the code below but cant get it right
      can anyone help me



        • 1. Re: 2 where clauses
          Dan Bracuk Level 5
          This would be so much easier if you had a joining field in these two tables. Do you not have one, or did you just forget to type it in when posting your problem.

          By the way, when you have subqueries in your from clause, you have to give it an alias.
          • 2. Re: 2 where clauses
            richy2424 Level 1
            Hi there is no joining field as there is nothing the same in the tables, also table2 is always changing so cant join them i dont think..

            the sum i need i got wrong this is what i need to add

            total of (BonusMom + BonusTwt + BonusTrs) as overTotal
            where
            PlayersID = QOne or
            PlaYersID = QTwo or
            PlayersID = QThree

            what would the best way be
            • 3. Re: 2 where clauses
              Iceborer Level 1
              The first thing you should do is take the person responsible for creating this abomination to a secluded area and beat them with a baseball bat until the twitching stops. Stop them before they "design" again.

              Personally, I would abandon trying to figure out the SQL to make this give the result you're looking for. I would create an array of structures which hold the information from your table1 and then loop through table2 incrementing a set of per-player totals stored in another array of player structures. Then again, I'm not a masochist.

              Adam
              • 4. 2 where clauses
                paross1 Level 2
                What a nightmare. It looks like somebody was actually trying to create the worst possible example of a denormalized data model. Good luck with this mess. If it were up to me, I would scrap the whole thing and start over with a decent data model, and then your queries would be an order of magnitude simpler. If I was to teach a data modeling class, I would use this as the "Don't ever do anything like this" example.

                Phil
                • 5. Re: 2 where clauses
                  richy2424 Level 1
                  can someone plz help me out with the code to my solution? i have been playing around with this for weeks as i have only just started using coldfusion, i am not sure how this is done
                  • 6. Re: 2 where clauses
                    paross1 Level 2
                    Do you have any control over the database design? In other words, are you locked into your current data model or structure? If not, then you are in luck and can start over by creating a normalized data model, otherwise you are going to be doing nothing but trying to implement one work around on top of another to get this terrible design to work.

                    Phil
                    • 7. Re: 2 where clauses
                      Kronin555 Level 1
                      Richy,

                      You're asking for help with a solution to a problem that shouldn't exist. This data model is just plain bad. Fix the data model, and your solution presents itself...

                      You need 3 tables.

                      Players
                      Teams
                      PlayerToTeam

                      Anything related to a player goes in the Players table.
                      Anything related to a team goes in the Teams table.
                      Anything related to a player's association to a team goes in the PlayerToTeam table (along with, obviously, a playerid and teamid).

                      ** Players **
                      Player ID
                      Player Name
                      ....

                      ** Teams **
                      Team ID
                      Team Name
                      ....

                      ** PlayerToTeam **
                      Player ID
                      Team ID

                      If a Player can only be in one Team, then you can simply place a TeamID reference in the Players table and get rid of the PlayerToTeam table.

                      The terminology used to correct your data model is called Normalization
                      http://en.wikipedia.org/wiki/Database_normalization

                      Basically, your PlayerOne, PlayerTwo... PlayerEleven columns and QONE...QTHREE columns all reference the PlayerID in the Players table, so rather than have separate columns for each one of those references, you break that out into a mapping table (PlayerToTeam). What exactly do QONE,QTWO, and QTHREE represent? What do the Bonus___ columns represent in the Players table?

                      If we fix the data model, the code is much cleaner and easier (plus giving you the benefit of having a good data model in the future).
                      • 8. 2 where clauses
                        richy2424 Level 1
                        ok many thanks yes i can change the tables

                        ok so now i have


                        ** TeamsTable **
                        Team ID (AutoNumber+PrimaryKey)
                        Team Name (Text)
                        ....

                        ** PlayerToTeam **
                        Player ID (Number)
                        Team ID (Number)
                        ....

                        ** PlayersTable **
                        Player ID (AutoNumber)
                        Player Name (Text)
                        ....

                        ** PointsTable ** (this table keep a recotd of player points everymatch)
                        Player ID (Number)
                        Points (Number)
                        MatchID (Number)
                        HomeTeam (Text)
                        AwayTeam (Text)


                        and i have the relastionship as

                        TeamsTable.TeamID (TO) PlayerToTeam.TeamID

                        so say if i now wanted to show

                        TeamsTable.TeamName PointsTable.TotalPoints

                        how would i do this

                        is the table relationships correct?
                        • 9. Re: 2 where clauses
                          Kronin555 Level 1
                          You added in the Match table. I'd do this...

                          ** TeamsTable **
                          Team ID (AutoNumber+PrimaryKey)
                          Team Name (Text)
                          ....

                          ** PlayerToTeam **
                          Player ID (Number, foreign key reference to players.playerid)
                          Team ID (Number, foreign key reference to teams.teamid)
                          ....

                          ** PlayersTable **
                          Player ID (AutoNumber)
                          Player Name (Text)
                          ....

                          ** MatchTable ** (each match gets a record in this table, with any match-specific information going here like match date)
                          Match ID

                          ** MatchToTeam ** (mapping table linking teams to matches)
                          Match ID
                          Team ID
                          HomeTeam (boolean, true if this team is the Home Team for the match)

                          ** PointsTable ** (this table keep a recotd of player points everymatch)
                          Player ID (Number)
                          Match ID (foreign key reference to Matches.matchid)
                          Points (Number)


                          Now you can do things like:

                          Total points for a player, for all matches that player has ever been in:
                          select sum(points) from pointstable left join playerstable on (pointstable.playerid = playerstable.playersid) where playerstable.playername = 'My Name';

                          Total points for a team for all matches:
                          select sum(points) from pointstable left join matchtable on (pointstable.matchid = matchtable.matchid) left join MatchToTeam on (matchtable.matchid = matchtoteam.teamid) where teamstable.teamname = 'My Team Name';

                          Total points for both teams for a specific match:
                          select sum(points) as totalpoints, teamname from pointstable left join matchtable on (pointstable.matchid = matchtable.matchid) left join MatchToTeam on (matchtable.matchid = matchtoteam.teamid) left join teamstable on (matchtoteam.teamid = teamstable.teamid) where matchtable.matchid = 8 group by teamstable.teamid
                          • 10. 2 where clauses
                            paross1 Level 2
                            Perhaps something like this

                            SELECT t.TeamName, SUM(p.Points) AS TotalPoints
                            FROM TeamsTable t, PlayerToTeam pt, PlayersTable pl, Points p
                            WHERE t.TeamID = pt.TeamID
                            AND pt.PlayerID = pl.PlayerID
                            AND pl.PlayerID = p.PlayerID
                            GROUP BY t.TeamName

                            but this assumes that a player is on only one team. If a player can be on multiple teams, then you need to somehow correlate matches (MatchID) with Teams (TeamID).

                            Phil
                            • 11. 2 where clauses
                              richy2424 Level 1
                              Ok many thanks it working now, i have tables

                              **POINTSTABLE**
                              PlayerID
                              Points
                              BonusPoints

                              **TEAMSTABLE**
                              TeamID
                              TeamName
                              Q1
                              Q2
                              Q3
                              Q4

                              Q1, Q2, Q3, Q4 are all answers to 4 questions the values are PlayerID's

                              So what i now need to do is add this query to the query at the bottom of this page.

                              SELECT SUM(HH.BonusPoints) AS Total
                              FROM PointsTable HH, TeamsTable GG
                              WHERE HH.PlayerID = GG.Q1 OR
                              HH.PlayerID = GG.Q2 OR
                              HH.PlayerID = GG.Q3 OR
                              HH.PlayerID = GG.Q4

                              But the problem is that lets say
                              Q1 = 24
                              Q2 = 24
                              Q3 = 24
                              Q4 = 24

                              If BonusPoints = 200 for PlayerID "24"
                              then the sum only counts this once, where it need to it count four times, the the Total would be 800

                              can you help with this
                              Thanks
                              • 12. 2 where clauses
                                paross1 Level 2
                                This still "smells" like a possible normalization problem, but since you are only showing us how you are implementing your business rules, and not actually telling us what those rules are (and what you are actually trying to accomplish), it makes it very difficult to guess if your design is even close to being optimal. What are these "4 questions" that you refer to, and what are you actually trying to do?

                                Phil
                                • 13. 2 where clauses
                                  richy2424 Level 1
                                  ok the questions are

                                  1. choose a player of the match
                                  2. choose the top scorer
                                  3. choose the first scorer
                                  and there will be more at a later date

                                  these all give an extra 100 points to the total
                                  • 14. Re: 2 where clauses
                                    paross1 Level 2
                                    Your last statement and there will be more at a later date is a big hint that you have a major design flaw, as adding more questions in the future would require both a program change and a data model / database change, which is not ideal.

                                    You probably should consider migrating the "questions" out of the teams table and into its own "bonusLink" table, where you might have something like teamID, matchID, bonusID (a foreign key to a new bonusType table) and playerID. BonusType might have something like bonusID, description (which would correlate to your question), and bonusAmount, so that you can change bonus amounts by type, or add new bonus types and amounts, whenever you add a question. Then you could link team and match through bonusLink to bounsType calculate the total number of bonus points by adding the amounts from bonusType by team, match, and player.

                                    I haven't worked out all of the particulars, but this kind of design change would allow you to easily add new questions, and even alter the amount of bonus points awarded per "question" (using DML) without having to make any database table changes (DDL).

                                    Phil
                                    • 15. Re: 2 where clauses
                                      Kronin555 Level 1
                                      Those questions are tied to matches, not teams, so don't put them in the Teams table.

                                      ** Match **
                                      MatchID

                                      ** BonusQuestions **
                                      QuestionID
                                      QuestionText (for example: "First Scorer")

                                      ** MatchToBonus **
                                      MatchID
                                      QuestionID
                                      PlayerID
                                      • 16. 2 where clauses
                                        paross1 Level 2
                                        richy2424,

                                        You might want to take a little break to improve your understanding of proper data modeling, as this could save you tons of anguish on projects like this. Nothing like the headaches of having to code around a bad database design, and getting the data model right before writing a single line of code makes the job so much easier. Below are just a couple of links to online information that may be helpful.

                                        Introduction to Data Modeling

                                        Data Model Reference

                                        Phil
                                        • 17. 2 where clauses
                                          richy2424 Level 1
                                          ok many thanks i understand now.

                                          i have these tables


                                          **BonusTable**
                                          QuestionID
                                          PlayerID
                                          Question
                                          BonusAmount

                                          **TeamToBonus**
                                          TeamID
                                          QuestionID
                                          PlayerID

                                          so what i now need is to join these queries into one query

                                          **this one gets the bonuspoints**

                                          SUM(HH.BonusAmount) AS TotalBonus
                                          FROM BonusTable HH, TeamToBonus KK, TeamsTable FF
                                          WHERE HH.QestionID = KK.QusetionID AND
                                          KK.TeamID = FF.TeamID


                                          **these 3 gets the points**
                                          <cfquery name="QueryOne" datasource="luvcriket">
                                          SELECT
                                          HH.TeamName, HH.TeamID,
                                          SUM(FF.Points) AS TotalPoints
                                          FROM TeamsTable HH, PlayerToTeam KK, PointsTable FF
                                          WHERE HH.TeamID = KK.TeamID AND KK.PlayerID = FF.PlayerID
                                          GROUP BY HH.TeamName, HH.TeamID
                                          </cfquery>

                                          <cfquery name="QueryTwo" datasource="luvcriket">
                                          SELECT
                                          SUM(FF.BonusPoints) AS TotalBonusPoints
                                          FROM TeamsTable HH, PlayerToTeam KK, PointsTable FF
                                          WHERE HH.TeamID = KK.TeamID AND KK.QONE = 1
                                          </cfquery>

                                          <cfquery name="QueryJoin" dbtype="query">
                                          SELECT TeamName, TotalPoints, TotalBonusPoints, TeamID, SUM(TotalPoints+TotalBonusPoints) AS TOT
                                          FROM GetBon, GetBonGG
                                          GROUP BY TeamName, TotalPoints, TotalBonusPoints, TeamID
                                          </cfquery>
                                          • 18. Re: 2 where clauses
                                            paross1 Level 2
                                            I'm curious, why do you have both...

                                            **this one gets the bonuspoints**

                                            SUM(HH.BonusAmount) AS TotalBonus
                                            FROM BonusTable HH, TeamToBonus KK, TeamsTable FF
                                            WHERE HH.QestionID = KK.QusetionID AND
                                            KK.TeamID = FF.TeamID

                                            --and--

                                            <cfquery name="QueryTwo" datasource="luvcriket">
                                            SELECT
                                            SUM(FF.BonusPoints) AS TotalBonusPoints
                                            FROM TeamsTable HH, PlayerToTeam KK, PointsTable FF
                                            WHERE HH.TeamID = KK.TeamID AND KK.QONE = 1
                                            </cfquery>

                                            Phil
                                            • 19. Re: 2 where clauses
                                              richy2424 Level 1
                                              Sorry my mistake

                                              need to join these two tables

                                              **this gets the points**
                                              <cfquery name="QueryOne" datasource="luvcriket">
                                              SELECT
                                              HH.TeamName, HH.TeamID,
                                              SUM(FF.Points) AS TotalPoints
                                              FROM TeamsTable HH, PlayerToTeam KK, PointsTable FF
                                              WHERE HH.TeamID = KK.TeamID AND KK.PlayerID = FF.PlayerID
                                              GROUP BY HH.TeamName, HH.TeamID
                                              </cfquery>

                                              **this gets the Bonus points**
                                              SUM(HH.BonusAmount) AS TotalBonus
                                              FROM BonusTable HH, TeamToBonus KK, TeamsTable FF
                                              WHERE HH.QestionID = KK.QusetionID AND
                                              KK.TeamID = FF.TeamID
                                              • 20. 2 where clauses
                                                paross1 Level 2
                                                Hmmm, interesting. I assume that it is possible that you may have a situation where you may have a team with no bonus points, so I did an OUTER JOIN to the TeamToBonus and BonusTable and put everthing in a single query. This is just a SWAG and not guaranteed to work, but to give you an idea of possible direction.

                                                <cfquery name="QueryOne" datasource="luvcriket">
                                                SELECT t.TeamName,
                                                t.TeamID,
                                                SUM(p.Points) AS TotalPoints,
                                                SUM(ISNULL(b.BonusAmount, 0)) AS TotalBonus
                                                FROM TeamsTable t
                                                INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                LEFT JOIN TeamToBonus tb ON tb.TeamID = t.TeamID
                                                LEFT JOIN BonusTable b ON b.QuestionID = tb.QuestionID
                                                GROUP BY t.TeamName, t.TeamID
                                                </cfquery>

                                                Phil
                                                • 21. Re: 2 where clauses
                                                  richy2424 Level 1
                                                  ok thanks i am getting a sinax error

                                                  [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 't.TeamID = pt.TeamID INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID LEFT JOIN TeamToBonus tb ON tb.TeamID = t.TeamID LEFT JOIN BonusTable b ON b.QuestionID = tb.QuestionID'.
                                                  • 22. 2 where clauses
                                                    paross1 Level 2
                                                    Oh, bummer, I was hoping that you weren't using Access, which is very picky about syntax when using INNER, and especially OUTER, joins and needing parantheses to group the correct tables and columns. Also, you can't use ISUNLL() this way in Access. You can use a combination of IIF and ISNULL if you need to replace a NULL value with 0., such as IIF(ISNULL(b.BonusAmount) 0, b.BonusAmount)

                                                    I haven't used Access for quite some time, so you will have to play around with the placement of the parantheses around your JOIN statements until you get the right grouping (the HELP files in Access may give you some hints for placement with OUTER joins).

                                                    You might try them as all INNER JOIN statements just to see if you get something close to what you are looking for, keepig in mind that you won't be selecting any rows where your team have no bonus point. (You can use the old pre-ANSI SQL 92 syntax for inner joins where you list the tables in the FROM clause, and "join" them in the FROM.)

                                                    Phil
                                                    • 23. 2 where clauses
                                                      richy2424 Level 1
                                                      Hi thanks, i have changed to mysql now and it sort of works , i just get the wrong results, i think i need to add

                                                      LEFT JOIN BonusTable b ON b.Answer = tb.Answer

                                                      but not sure how to do it as the table name already exsits in the query?

                                                      <cfquery name="QueryOne" datasource="luvcriket">
                                                      SELECT t.TeamName,
                                                      t.TeamID,
                                                      SUM(p.Points) AS TotalPoints,
                                                      SUM(b.BonusAmount) AS TotalBonus
                                                      FROM TeamsTable t
                                                      INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                      INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                      LEFT JOIN MatchToBonus tb ON tb.TeamID = t.TeamID
                                                      LEFT JOIN BonusTable b ON b.QuestionID = tb.QuestionID
                                                      GROUP BY t.TeamName, t.TeamID
                                                      </cfquery>
                                                      • 24. Re: 2 where clauses
                                                        Level 7
                                                        Hi thanks, i have changed to mysql now and it sort of works , i just get the
                                                        wrong results, i think i need to add

                                                        LEFT JOIN tb.PlayerID = b.Answer

                                                        but not sure how to do it as the table name already exsits in the query?

                                                        <cfquery name="QueryOne" datasource="luvcriket">
                                                        SELECT t.TeamName,
                                                        t.TeamID,
                                                        SUM(p.Points) AS TotalPoints,
                                                        SUM(b.BonusAmount) AS TotalBonus
                                                        FROM TeamsTable t
                                                        INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                        INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                        LEFT JOIN MatchToBonus tb ON tb.TeamID = t.TeamID
                                                        LEFT JOIN BonusTable b ON b.QuestionID = tb.QuestionID
                                                        GROUP BY t.TeamName, t.TeamID
                                                        </cfquery>

                                                        • 25. Re: 2 where clauses
                                                          paross1 Level 2
                                                          Like this?

                                                          <cfquery name="QueryOne" datasource="luvcriket">
                                                          SELECT t.TeamName,
                                                          t.TeamID,
                                                          SUM(p.Points) AS TotalPoints,
                                                          SUM(b.BonusAmount) AS TotalBonus
                                                          FROM TeamsTable t
                                                          INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                          INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                          LEFT JOIN MatchToBonus tb ON tb.TeamID = t.TeamID
                                                          LEFT JOIN BonusTable b ON b.QuestionID = tb.QuestionID
                                                          AND b.Answer = tb.PlayerID
                                                          GROUP BY t.TeamName, t.TeamID
                                                          </cfquery>

                                                          Phil
                                                          • 26. Re: 2 where clauses
                                                            richy2424 Level 1
                                                            ok i am still getting wrong results, the answer should be 100 but i am getting 300

                                                            when i do the query below on it own it works fine, get the right results, as soon as i add it to the main query i get wrong results?

                                                            <cfquery name="Query2" datasource="luvcriket">
                                                            SELECT
                                                            SUM(b.BonusAmount) AS TotalBonus
                                                            FROM
                                                            MatchToBonus tb, BonusTable b, TeamsTable t
                                                            WHERE tb.TeamID = t.TeamID AND
                                                            b.Answer = tb.Answer AND
                                                            b.QuestionID = tb.QuestionID
                                                            GROUP BY t.TeamName
                                                            </cfquery>

                                                            any ideas what i can do

                                                            Thanks
                                                            • 27. Re: 2 where clauses
                                                              paross1 Level 2
                                                              Is only TotalBonus incorrect and TotalPoints is OK, or are both values incorrect?

                                                              Phil
                                                              • 28. Re: 2 where clauses
                                                                richy2424 Level 1
                                                                yes total points are ok, its just the total bonus that gives wrong values
                                                                • 29. 2 where clauses
                                                                  paross1 Level 2
                                                                  On 5/29 you asked how to add LEFT JOIN tb.PlayerID = b.Answer, yet in the query that you said "works", you have b.Answer = tb.Answer, so which is it???? You might try this:

                                                                  <cfquery name="QueryOne" datasource="luvcriket">
                                                                  SELECT t.TeamName,
                                                                  t.TeamID,
                                                                  SUM(p.Points) AS TotalPoints,
                                                                  SUM(ISNULL(b.BonusAmount, 0)) AS TotalBonus
                                                                  FROM TeamsTable t
                                                                  INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                                  INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                                  LEFT JOIN MatchToBonus tb ON tb.TeamID = t.TeamID
                                                                  LEFT JOIN BonusTable b ON b.QuestionID = tb.QuestionID
                                                                  AND b.Answer = tb.Answer
                                                                  GROUP BY t.TeamName, t.TeamID
                                                                  </cfquery>

                                                                  --or--

                                                                  <cfquery name="QueryOne" datasource="luvcriket">
                                                                  SELECT t.TeamName,
                                                                  t.TeamID,
                                                                  SUM(p.Points) AS TotalPoints,
                                                                  SUM(b.BonusAmount) AS TotalBonus
                                                                  FROM TeamsTable t
                                                                  INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                                  INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                                  INNER JOIN MatchToBonus tb ON tb.TeamID = t.TeamID
                                                                  INNER JOIN BonusTable b ON b.QuestionID = tb.QuestionID
                                                                  AND b.Answer = tb.Answer
                                                                  GROUP BY t.TeamName, t.TeamID
                                                                  </cfquery>

                                                                  (It is hard to make suggestions when you keep changing what you are asking for.)

                                                                  Phil
                                                                  • 30. Re: 2 where clauses
                                                                    richy2424 Level 1
                                                                    ok sorry i just changed the column name so i didnt get confused

                                                                    the first query i get a syntax error
                                                                    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 0)) AS TotalBonus FROM TeamsTable t INNER JOIN PlayerToTeam pt ON t.TeamID = ' at line 4

                                                                    the second query gives an output but an even worse silly result
                                                                    • 31. Re: 2 where clauses
                                                                      paross1 Level 2
                                                                      Drop the ISNULL part from SUM(ISNULL(b.BonusAmount, 0)) AS TotalBonus to make SUM(b.BonusAmount) AS TotalBonus, but I'm afraid that you are still going to get an invalid count for bonus point. I'm afraid that you are missing something, but I can't see it as I am only seeing bits and pieces of your entire process.

                                                                      Phil
                                                                      • 32. Re: 2 where clauses
                                                                        richy2424 Level 1
                                                                        k these are the tables i have

                                                                        **BonusTable**
                                                                        QuestionID
                                                                        Question (the actual written question)
                                                                        BonusAmount (Bonus point if question is answered corect)
                                                                        Answer (Answer to the question, this will be null until the event as happened)

                                                                        **MatchToBonus**
                                                                        TeamID
                                                                        QuestionID
                                                                        Answer (The answer that the user has given)

                                                                        **TeamsTable**
                                                                        TeamID
                                                                        TeamName

                                                                        so what i want to do is show the "BonusTable.BonusAmount" for each team if

                                                                        BonusTable.QuestionID = MatchToBonus.QuestionID AND
                                                                        BonusTable.Answer = MatchToBonus.Answer AND
                                                                        TeamsTable.TeamID = MatchToBonus.TeamID

                                                                        This works when is a single query but not sure how to join them.

                                                                        many thanks

                                                                        richy
                                                                        • 33. 2 where clauses
                                                                          paross1 Level 2
                                                                          I think that you are having problems double matching on the BonusTable, as it is entirely unecessary to match on QuestionID and Answer. What do you get when you try this one?

                                                                          <cfquery name="QueryOne" datasource="luvcriket">
                                                                          SELECT t.TeamName,
                                                                          t.TeamID,
                                                                          SUM(p.Points) AS TotalPoints,
                                                                          (SELECT SUM(b.BonusAmount)
                                                                          FROM BonusTable b
                                                                          INNER JOIN MatchToBonus tb ON b.QuestionID = tb.QuestionID
                                                                          WHERE tb.TeamID = t.TeamID) AS TotalBonus
                                                                          FROM TeamsTable t
                                                                          INNER JOIN PlayerToTeam pt ON t.TeamID = pt.TeamID
                                                                          INNER JOIN PointsTable p ON pt.PlayerID = p.PlayerID
                                                                          GROUP BY t.TeamName, t.TeamID
                                                                          </cfquery>

                                                                          Phil
                                                                          • 34. Re: 2 where clauses
                                                                            richy2424 Level 1
                                                                            ok this works when only one question is there i think maybe i need another table?

                                                                            in the MatchToBounus table i have

                                                                            TeamID QuestionID Answer
                                                                            1 1 23
                                                                            1 2 12
                                                                            1 3 14
                                                                            2 1 22
                                                                            2 2 27
                                                                            2 3 23

                                                                            so is this correct? if so i do need the questionID to match the BonusTable.QuestionID

                                                                            or do i have another table with question aswers in and match that questionid?
                                                                            • 35. Re: 2 where clauses
                                                                              paross1 Level 2
                                                                              Wow, I think that you lost me... what the heck is the relationship between question and answer? I feel pretty sure that you probably wouldn't want ot have have both together in the MatchToBonus table, but I've lost track of your data model and what the relationships are between the entities, especially since it hasn't been presented in one piece, and it seems to be constantly evolving.

                                                                              Phil
                                                                              • 36. 2 where clauses
                                                                                richy2424 Level 1
                                                                                its not evolving, all i need is a table where i have a question, then when i get the answer to the question ie player of the match
                                                                                i enter it, so i have the table BonusTable for that

                                                                                **BonusTable**
                                                                                Question (who will be the player of the match?)
                                                                                QuestionID
                                                                                Answer (the answer to the question, when i know who it is, this will be a playersID, i will be adding this at a later stage when the match has been played)
                                                                                BonusAmount (Bonus Points)

                                                                                Then i have a table where the user (TeamID) answers the question
                                                                                **MatchToBonus**
                                                                                TeamID
                                                                                QuestionID (This ID matches the QuestionID in the BonusTable)
                                                                                Answer (this is the users answer to the question this will be a playerID)

                                                                                So i need to add up all "BonusAmount" where the questionID and the Answers to match up

                                                                                so a users may have
                                                                                **MatchToBonus**
                                                                                TeamID (1), QuestionID (1), Answer (23)
                                                                                TeamID (1), QuestionID (2), Answer (12)
                                                                                TeamID (1), QuestionID (3), Answer (24)

                                                                                TeamID (2), QuestionID (1), Answer (2)
                                                                                TeamID (2), QuestionID (2), Answer (22)
                                                                                TeamID (2), QuestionID (3), Answer (2)

                                                                                hope this helps
                                                                                • 37. Re: 2 where clauses
                                                                                  paross1 Level 2
                                                                                  I looked back at the previous posts, and nowhere did I find where you actually explained what it is that you are trying to do (business rules). I see a lot of attempted implementation, where we've tried ot reverse engineer to figure out what you are doing, but I am still missing something. For instance, what do the questions and answers actually have to do with counting bonus points? Doesn't it just boil down to what category the bonus is, what team or player gets the pints, and a process of adding the total by team?

                                                                                  Phil
                                                                                  • 38. 2 where clauses
                                                                                    richy2424 Level 1
                                                                                    ok a team will have 5 questions to answer

                                                                                    ie
                                                                                    Q1. who will be the player of the match?
                                                                                    Q2. who will be the top scorer?
                                                                                    etc
                                                                                    they will get 100 bonus points if there anwser is correct

                                                                                    so somewhere i need to match questionID and the answers for both the users Answer and the actual Answer
                                                                                    • 39. Re: 2 where clauses
                                                                                      paross1 Level 2
                                                                                      Where are you storing the actual answer verses the user's answer, and how are you determining if the answer is correct? I suppose that there are a couple of ways that you could do this,such as putting a "test" in the subselect that SUMs the bonus points so that points are added only if the answers are equal, etc.

                                                                                      Phil
                                                                                      1 2 Previous Next