Skip to main content
A
Anonymous
May 22, 2006
Question

2 where clauses

  • May 22, 2006
  • 2 replies
  • 4249 views
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



This topic has been closed for replies.

2 replies

A
Anonymous
June 3, 2006
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
P
paross1
Participating Frequently
June 3, 2006
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
A
Anonymous
June 4, 2006
ok many thanks, i have changed my data model and this now works.
the only thing i need to do now is the (totalpoints) needs to include the (totalbonus)

i tried adding to ny query
SUM(p.Points+b.BonusPoints) AS TotalPoints
but i get an error can you help?

<cfquery name="QueryOne" datasource="luvcriket">
SELECT t.TeamName,
t.TeamID,
SUM(p.Points) AS TotalPoints,
(SELECT SUM(b.BonusPoints)
FROM BonusTable b
WHERE b.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>
D
Dan_Bracuk
Inspiring
May 22, 2006
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.
A
Anonymous
May 22, 2006
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
A
Anonymous
May 22, 2006
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices