sql - Counting rows in multiple tables -

-

I have mysql database which is tracking hockey statistics What I want to do is given by each player in a question The number of goals as well as the number of games that they have played in the game. I am using the Zend Framework and the query I created is: 

  SELECT `p`. *, `Pxt`. Jersey_Number`, count (pxg.player_x_game_id) In the form of `sports`, the count (targets.scoring_id) as` target`, counting `assists.scoring_id` as` players`` `` `` `` `` Join 'players_x_teams` to join `p_player_id = pxt.player_id` helps players' join teams_x_seasons 'at pxt.team_id = txs as` txs` .team_id join INNER `season' A S`tx Season_ID = S. Season_ID Inner Join 'Games' AS 'G' On On G Season_id = es. Join Season_Ind Inner 'Players_X_Game' AS 'PGG' on pxg.game_id = G.game_id and pxg.player_id = p.player_id LEFT JOIN 'Goals' on goals as' scoring`. Game_id = g.game_id and aim. Scorer_ID = p.player_id LEFT JOIN 'scoring `AS' help 'helps me_id = g.game_id and (assists.assist1_id = p.player_id or support .assist2_id = p.player_id) WHERE (pxt.team_id = 1) and (Txs.season_id = '23') and (pxt.date_added & lt; = s.end_date or pxt.date_added is zeros) and (pxt.date_removed & gt; = s.start_date or pxt.date_removed faucet) P`.player_id`

This query returns me data, but my countdown is off. 

  + ----------- + --------------- + ------- + ---- --- + --------- + | Player_id | Jersey_number | Sports | Goals | Help | + ----------- + --------------- + ------- + ------- + ----- ---- + | 2 | 3 | 7 | 1 | 3 | | 3 | 19 | 6 | 1 | 0 | | 8 | 8 | 7 | 3 | 2 | | 9 | 11 | 13 | 10 | 8 | | 11 | 96 | 6 | 1 | 3 | | 12 | 14 | 6 | 0 | 3 | | 13 | 7 | 6 | 0 | 1 | | 115 | 39 | 9 | 6 | 2 | | 142 | 68 | 6 | 0 | 1 | | 143 | 30 | 6 | 0 | 0 | | 150 | 41 | 11 | 11 | 5 | | 185 | 17 | 6 | 6 | 3 | | 225 | 97 | 4 | 1 | 3 | + ----------- + --------------- + ------- + ------- + ----- ---- +

The most databases in this dataset are 6 which should be present, but as you can see that I am getting extras if I remove my targets I adjust the query and assists in counting my game, it gets right, in fact if I only select one of my calculated rows then I always get the correct number, I What a time I would have joined in a second or third number, if my numbers are incomplete. Am i doing wrong

Since you are doing multiple pairs, which can match each one of the more rows next To join, you need to add the different to your count . Try it: 

  SELECT `p` *, `Pxt`. Jersey_Number`, counting (separate pxg.player_x_game_id) in the form of `game`, calculations (different targets. Scoring_id) AS 'goals`, count (different help.scoring_ID) as` players` form of `help` In the 'P' Inner form of `players` in the 'players_x_tem' at the 'pxt` p. Player_id = pix. Join as 'Team_id = txs.team_id INNER `player' Ace` tx as player_id inner 'teams_x_season'. Season_ID = S. Season_ID Inner Join 'Game' AS 'G' On On G Season_ID = S. Season_Id Join INNER 'Players_X_Gen' AS 'PGG' on PGG.game_id = g.game_id and pxg.player_id = p.player_id LEFT JOIN 'Goals' on Goals' Goals' as' scoring` game_id = g.game_id and target . Scorer_ID = p.player_id LEFT JOIN 'Scoring' helps AS 'help .game_id = g.game_id and (assists.assist1_id = p.player_id or support .assist2_id = p.player_id) WHERE (pxt.team_id = 1) And (txs.season_id = '23') and (pxt.date_added & lt; = s.end_date or pxt.date_added tap) and (pxt.date_removed & gt; = s.start_date or pxt.date_removed is zero) by group `P`.player_id`

Comments

Post a Comment

Popular posts from this blog

asp.net - Javascript/DOM Why is does my form not support submit()? -

-
This is my first time working with asp.net and javascript, so I do not know very good web resources, or Much about javascript debugging I have to find out that on line oFormObject.submit (); Microsoft JScript Runtime Error: Object does not support this property or method I am using the link to work as a button because I am terrible in aesthetics and I think That link will look more professional than a table of 50 buttons. I have read that this solution can cause problems with browsers due to pre-rendering of my links and many additional traffic and problems, but CSS gives me a button like a link, which has issues of alignment and difference seemed to. & lt ;! DOCTYPE HTML PUBLIC "- // W3C // DTD HTML 4.01 // N" "http://www.w3.org/TR/html4/strict.dtd"> & Lt; Html xmlns = "http://www.w3.org/1999/xhtml" & gt; & Lt; Head & gt; & Lt; Link rel = "stylesheet" type = "text / css" href = "defect differen
Read more

sockets - Delphi: TTcpServer, connection reset when reading -

-
I am trying to implement one for Delphi, but there are some problems with communication. Fitnesse will start my process, and give me a portmember as a command line argument. Then I should make a socket on the port number given to me, and fitness will connect to that port I am using a TCPCP server for the job: < Pre> TcpServer1.LocalPort: = ParamStr (ParamCount); TcpServer1 Active: = true; On OnEXEPT () -Event, I send the protocol version to use as specified in the protocol. Process TForm1.TcpServer1Accept (From: TOBject; ClientSocket: TCustomIpClient); Var s: ansistring; Start clientSocket. Syndrome ('slim - v 0.0', # 10); SetLength (S, 6); ClientSketts Receavebook (S, 6); End; When I call ReceiveBuf (), the process ends, and fitness throws an exception: java.net.SocketException: connection Reset I have seen what sent and received. It shows that my code sends the protocol version, fitness sends back a message, and when I try to get this message
Read more

javascript - Classic ASP "ExecuteGlobal" statement acting differently on two servers -

-
I have a strange problem that I really do not know. In addition to developing a new ASP.NET site, I have to support the old "classic ASP" site. It is written in VBScript with a batch of JavaScript functions. Many of the JavaScript functions include 'files' Everything is fine on our production system (which security is controlled by another group - this is a military installation) Debugging them once did not work I). However, I have a fix for a vague bug that needs to be fully tested and some functions do not run under the "local" copy of the website that runs on my PC. For the record, I am running IIS 5.1 on XP. The problem is that some Javascript functions are running "OK" on the production server, but when they use it as a server, they are not defined on my PC what is happening that last Programmer, for some reason, has decided to put some functions in a series of files, which according to the observations, imitate the "need_once
Read more