Difficult Query (but doable in Access)

Mitch · October 30th, 2003, 10:51 PM

I have a query in MS Access that I am trying to convert to MySQL for use on my High School Hockey team's stat page. I am trying to convert my MS Access database over to PHP and MySQL but the queries are killing me (I use two seperate queries nested within one main query and they each use parameters for season and team).

Here are the tables and the needed fields (I think)

PH_SCORE_HEADER
  StatID
  SeasonID

PH_SCORE_DETAIL
  StatID
  1stAssist (matches PLAYERID)
  2ndAssist (matches PLAYERID)

PH_PLAYERS
  PLAYERID
  TeamID

I need to total the players 1stAssists and 2ndAssists (individually) where the SeasonID=X and the TeamID=Y so that I know how many 1st assists the player had as well as how many 2nd assists they had and then a grand total of 1st+2nd assists that player had.

I have tried to re-write my MS Access query as one big query and I can't get it to work.

linuxboy · November 3rd, 2003, 02:03 PM

Just to get this straight, you want to do the above in one query the value you have inside of the 1st & 2nd Assist Columns have a value of a player_id.

Mitch · November 3rd, 2003, 04:15 PM

I think I want to do it in one query, becaue I don't know how to do it in multiple queries, outside of Access that is.

The data in PH_SCORE_DETAIL might look like this:

StatID 1stAssist 2ndAssist
94 74 647
94 874 74
120 74 0

Which translates into Player number 74 had a First Assist and a Second Assist in game 94 and also had a First Assist in game 120

I want to show that player 74 had a total of 2 First Assists and 1 Second Assist and a Grand Total of 3 Assists (all combined StatID's) for the season.

Jonax · November 4th, 2003, 09:04 AM

I haven't tested this against a MySQL-db, but I think it'll work...
Note: You need to replace the two Xs and the Y...

Code:

SELECT PlayerId,

(SELECT COUNT(*) FROM PH_SCORE_DETAIL D1 INNER JOIN PH_SCORE_HEADER H1 ON D1.StatId = H1.StatId
WHERE [1stAssist] = P.PlayerId AND H1.SeasonId = X) AS FirstAssist,

(SELECT COUNT(*) FROM PH_SCORE_DETAIL D2 INNER JOIN PH_SCORE_HEADER H2 ON D2.StatId = H2.StatId
WHERE [2ndAssist] = P.PlayerId AND H2.SeasonId = X) AS SecondAssist

FROM PH_PLAYERS P WHERE P.TeamId = Y

Mitch · November 4th, 2003, 02:29 PM

Well that did not work, I get a Parse Error. I checked my version 4.0.14-g and the notes on sub queries and found this:

Code:

Subqueries are supported in MySQL version 4.1.

Up to version 4.0, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported. You can, however, use the function IN() in other contexts. 

You can often rewrite the query without a subquery: 

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as: 

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
The queries: 

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
                                       WHERE table1.id=table2.id);
Can be rewritten as: 

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
                                       WHERE table2.id IS NULL;

So, I don't know if that is the problem, if it is, I'm still not sure how to re-write what you've given.