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

2 where clauses

Guest
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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



TOPICS
Advanced techniques

Views

3.0K

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 ,
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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.

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
Guest
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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

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 ,
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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

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 ,
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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

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
Guest
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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

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 ,
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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

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
Advocate ,
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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).

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
Guest
May 22, 2006 May 22, 2006

Copy link to clipboard

Copied

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?

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
Advocate ,
May 23, 2006 May 23, 2006

Copy link to clipboard

Copied

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

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 ,
May 23, 2006 May 23, 2006

Copy link to clipboard

Copied

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

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
Guest
May 23, 2006 May 23, 2006

Copy link to clipboard

Copied

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

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 ,
May 24, 2006 May 24, 2006

Copy link to clipboard

Copied

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

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
Guest
May 24, 2006 May 24, 2006

Copy link to clipboard

Copied

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

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 ,
May 24, 2006 May 24, 2006

Copy link to clipboard

Copied

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

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
Advocate ,
May 24, 2006 May 24, 2006

Copy link to clipboard

Copied

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

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 ,
May 24, 2006 May 24, 2006

Copy link to clipboard

Copied

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

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
Guest
May 24, 2006 May 24, 2006

Copy link to clipboard

Copied

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>

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 ,
May 25, 2006 May 25, 2006

Copy link to clipboard

Copied

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

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
Guest
May 25, 2006 May 25, 2006

Copy link to clipboard

Copied

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

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 ,
May 26, 2006 May 26, 2006

Copy link to clipboard

Copied

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

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
Guest
May 29, 2006 May 29, 2006

Copy link to clipboard

Copied

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'.

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 ,
May 29, 2006 May 29, 2006

Copy link to clipboard

Copied

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

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
Guest
May 29, 2006 May 29, 2006

Copy link to clipboard

Copied

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>

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 ,
May 29, 2006 May 29, 2006

Copy link to clipboard

Copied

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>

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