Please help with my query

[scottyxy]

Hey guys im new to the forum loocated it when looking for help with this query, i know the basics of SQl but want to further my knowledge.Here is the situation I have 4 tables MEMBERS, WAR, VCLSCORES and OTHERSCORESIm basically just mucking around creating a stats table that displays scores in flags for all clan wars played by my clan. the WAR table consists of WarIdMapModeTime ClanPlayedVCLSCORES table consists ofWarIdIdNo (used to retrieve member details from member table)KillsDeathsFlagsOTHERSCORES consists of WarIdNameKillsDeathsFlagsI am trying to create a query that will grab all the details from WAR and a SUM of the flags columns from each VCLSCORES and OTHERSCORES for every war. I can get the query to work for WAR and VCLSCORES but as soon as i try to get the SUM for OTHERSCORES aswell i get a whole heaps of crap numbers.This is the query i am using to get the two tables workingSELECT WAR.WarId, WAR.Map, WAR.Mode, WAR.Time, WAR.ClanPlayed, SUM(VCLSCORES.Flags)FROM VCLSCORES INNER JOIN WAR ON VCLSCORES.WarId = WAR.WarIdWHERE VCLSCORES.WarId = WAR.WarIdGROUP BY WAR.WarId Any ideas how to add a SUM for OTHERSCORES into this query????Please help.Thanks guys

[Kcarson]

Perhaps something like this....

SELECT War.WarID, War.Map, War.Mode, War.Time, War.ClanPlayed, Sum(VCLScores.Flags), Sum(OtherScores.Flags)FROM War JOIN VCLScores ON VCLScores.WarID = War.WarIDJOIN OtherScores ON OtherScores.WarID = War.WarIDGROUP BY War.WarID

Let me know if you have any questions, or if this doesn't work for some reason.

[scottyxy]

I tried the query you provided and i am still getting really high numbers. The query works but the numbers being returned are not accurate for some reason, and i dont know why?

[Guest mhasanyousaf]

this is serious problem in u query.please write a qury agine.my e mail.is hasan_yousaf2004@hotmail.com

[Skemcin]

this is serious problem in u query.please write a qury agine.my e mail.is  hasan_yousaf2004@hotmail.com

I'm not sure what the meaning of this post is - please specify or it will be removed. Communication is encourage to stay within the realm of the discussion board so that others can help troubleshoot and learn. Solutions will not be e-mailed directly to you.Thanks.P.S. Please take the time to proof-read your posts so that they can be clearly understood.

[Kcarson]

I tried the query you provided and i am still getting really high numbers. The query works but the numbers being returned are not accurate for some reason, and i dont know why?

<{POST_SNAPBACK}>

Hmm....not sure what to say . Are there any problems with the data you have stored in the OtherScores table? Are you sure the Flag column is an integer/number type?To check the data I would suggest running a query something like this...

SELECT flagsFROM OtherScores ORDER BY flags DESC

That way you can see what the largest numbers are in the table and maybe that would explain your high numbers, well its a start at least that might narrow down the field of potential problems.Let me know what you find out.

[scottyxy]

Hmm....not sure what to say . Are there any problems with the data you have stored in the OtherScores table? Are you sure the Flag column is an integer/number type?To check the data I would suggest running a query something like this...

SELECT flagsFROM OtherScores ORDER BY flags DESC

That way you can see what the largest numbers are in the table and maybe that would explain your high numbers, well its a start at least that might narrow down the field of potential problems.Let me know what you find out.

<{POST_SNAPBACK}>

I have tried as you suggested, but it still does not explain my confusion.I am able to get the SUM for flags column from both VCLSCORES and OTHERSCORE for each war seperately but when i try to get them both in the same query thats when my numbers become stupidly high.For some reason the first row of the query gets multiplied by 4 and all the following columns get multiplied by 2, at least thats the patern i am picking up :-( Nothign ever goes right for me. We do my queries work individually but not when i try and get BOTH SUMS togwether??

[Kcarson]

Very strange problem

For some reason the first row of the query gets multiplied by 4 and all the following columns get multiplied by 2, at least thats the patern i am picking up :-(

Can you be more descriptive in your explanation of what is happening when you include the sums for both tables? Maybe include what the sums are if done individually and what they are if done together?Also, what happens if you use the same query given earlier, but instead of showing the sum(OtherScores.Flag) you just showed OtherScores.Flag. Would your sum(VCLScores.Flag) still be off, or is it correct now?

[Kcarson]

Ok, I was able to recreate the problem at work today, and I am not positive of a solution , but I figured if I posted a explanation of the problem, maybe somebody else could help :)ExampleJoin between two tables

WarID   VCLScores.Flags1               52               73               4

Join between three tables

WarID   VCLScores.Flags   OtherScores.Flags1               5                            21               5                            22               7                            62               7                            63               4                            13               4                            1

Now for the explanation, when the joins are performed between two tables, then the WarID is matched between both and you get the correct sum, but when you perform the join between all three tables it will first do the join between War and VCLScores (but what should it put in the columns for OtherScores? well, it puts includes the values for OtherScores as well) and then it will do the join between OtherScores and War, but once again what should it include in the VCLScores column, and it includes the correct values there as well. So basically, it is including the rows multiple times.In my experiment, I had 46 rows when I performed a join between 2 tables, 6000 when I included the third, needless to say the sums were off the second time

[scottyxy]

Yeha thanks heaps for the explanation, thats pretty much what im trying to say i just didnt know how to say it. When working with 2 of the tables ALL the sums work fine. for boths (WARS and VCLSCORES) and (WARS and OTHERSCORES) Add the 3rd table to the equation and like you said the sums get multiplied.

[Kcarson]

Okay, Scott I figured out how to get it all into one query....kind of ugly and not very efficient probably but it works....Here it is:

SELECT War.WarID, War.Map, War.Mode, War.Time, War.ClanPlayed, Sum(VCLScores.Flags),    (SELECT Sum(OtherScores.Flags) FROM War War1     JOIN OtherScores ON OtherScores.WarID = War1.WarID    WHERE OtherScores.WarID = War.WarID)FROM War JOIN VCLScores ON VCLScores.WarID = War.WarIDGROUP BY War.WarID

Explanation: rather than have an additional column Sum(OtherScores.Flags), I added a column containing the query with War and OtherScores joined with just one result sum(OtherScores.Flags). I then tell it to only produce the results when the OtherScores WarID matches the War.WarID contained outside the current statement (the one joined with VCLScores).....Like I said, its not pretty and I would like to know if anybody could do it better and I really mean that .Well, let me know if you have any problems with it, or if you need a better explanation of things, or any other questions, I would be glad to help