CodingForums.com - Need Help With A Table Join

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Need Help With A Table Join (http://www.codingforums.com/showthread.php?t=289397)

Need Help With A Table Join

I am trying to join two tables so that I can grab the information and order them by the members points. Here is the query I am trying to use:

Code:

      

$members_info = mysqli_query($connection, "

SELECT 

team_member,

stage,

points

FROM team_member_scores

RIGHT JOIN team_members

ON team_members.team_id = team_member_scores.team_id

WHERE game_id = '".$game_id_info."'

AND team_member_scores.team_id = '".$team_id."'

GROUP BY team_member

ORDER BY points DESC

LIMIT 5") 

or die(mysqli_error($connection));

This query pulls out all the team members correctly, but instead of grouping them and then ordering them by their points, it seem to just sum the points for all the members and lists that same summed number for every member. I need to pull out the info from both tables because I need to order them in a specific way, and not all team members will show up in the team_member_scores table if they didn't participate in the last game.

I would like to pull every match from the team members table that has the team id, regardless if they are in the team_member_scores table. I can get it to pull out the right information only from the team_members table. I can run two queries, but then I wont' get the sort order I am hoping for.

Anyway, if you need more info, please let me know. Any help and suggestions are greatly appreciated.

What POSSIBLE reason do you have for using GROUP BY in that query?

That's the culprit.

KILL IT.

Any other DB wouldn't allow you to use it like that, but MySQL not only allows it but makes it have some very weird side effects.

***********

Incidentally, your use of RIGHT JOIN there is incorrect. Or, rather, because of the rest of the query your RIGHT JOIN is silently being converted into an INNER JOIN.

You can *NOT* use any condition on the DEPENDENT table in an outer join in the WHERE clause. Such conditions *MUST* be made in the ON clause. (Sole exceptions: tests for NULL or NOT NULL.)

So:

Code:

SELECT team_member, stage, points

FROM team_member_scores

RIGHT JOIN team_members

    ON team_members.team_id = team_member_scores.team_id

       AND team_member_scores.team_id = '".$team_id."'

WHERE game_id = '".$game_id_info."'

ORDER BY points DESC LIMIT 5

Though I would bet a latte that you do not need or want the apostrophes around '$team_id' or '$game_id_info'. You should never put apostrophes around NUMBERS if you are comparing with or assigning to numeric fields.

May I *strongly* suggest that before you use a query in your PHP code you first check it out using some DB query tool? MySQLWorkbench, perhaps. Or even ugly MySQLPHPAdmin or whatever.

Thanks again Old Pedant, I learn something new each time I post on this forum, and for that I am very grateful. :thumbsup:

The only issue is that it shows the same amount of points for every team member pulled from the table. In other words, it doesn't seem to assign the right points to the right member when pulling them from the table, if that makes sense.

Can you show some sample data?

Maybe just the data for one team?

Quote:

Originally Posted by Old Pedant (Post 1318908)

Can you show some sample data?

Maybe just the data for one team?

Sure, and sorry for not providing this in the first post:

The team_member_scores has the following columns and data:

username - game_id - team_id - points
Fred - 1 - 1 - 200
Joe - 1 - 1 - 600
Aaron - 1 - 2 - 500

and the team_members has the following info:

team_id - username - stage - join_date
1 - Fred - 1 - timstamp

The username is being used as the foreign key in the team_member_scores table (I didn't design the team_members table, but I can redesign it if you think it would be better.)

I hope that clears it up.

Quote:

The username is being used as the foreign key in the team_member_scores table

Ummm...no, it is not. At least not in your query.

And that's surely the problem.

Foreign keys are *NOT* automatically used in queries. Ever. They *MUST* be explicitly specified in the JOIN or they will be ignored. A foreign key DECLARATION in the table is only useful for preserving data integrity, not for queries.

The real problem here is that the team_id field in the team_member_scores table is extraneous. It was leading you down the garden path!

WHERE, though, did the field member_name come from? In what you just showed me, there is no such field? Now you seem to be using username instead? I'll go with that, but...

May I try completely rewriting the query, then?

Code:

$sql = "

SELECT M.username, M.stage, S.points

FROM team_members AS M

LEFT JOIN team_member_scores AS S ON M.username = S.username

WHERE M.team_id = $team_id AND M.game_id = $game_id_info

ORDER BY points DESC LIMIT 5";

(You don't need to use string concatenation with the period; PHP allows you to drop PHP variables into the middle of a quoted string so long as your use "..." instead of '...' for the quotes.)

Now, that will *NOT* get you the SUM of points for each team_member. That will just show you who scored the most points a SINGLE GAME.

Since you have limited the query to just one game_id that's probably okay.

Variations on that:

Code:

$sql = "

SELECT M.username, M.stage, SUM(S.points) AS totalPoints

FROM team_members AS M

LEFT JOIN team_member_scores AS S ON M.username = S.username

WHERE M.team_id = $team_id 

GROUP BY M.username, M.stage

ORDER BY points DESC LIMIT 5";

And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that AND M.game_id = ... here. Note also that both fields are needed in the GROUP BY, as shown.)

And almost forgot to say: That will show all team members, even if they have no scores at all in the team_member_scores table. Your query would have, too, except for the one bogus condition in the WHERE clause and if the JOIN had been done correctly.

Quote:

Originally Posted by Old Pedant (Post 1318940)

Code:

$sql = "

SELECT M.username, M.stage, S.points

FROM team_members AS M

LEFT JOIN team_member_scores AS S ON M.username = S.username

WHERE M.team_id = $team_id AND M.game_id = $game_id_info

ORDER BY points DESC LIMIT 5";

Code:

$sql = "

SELECT M.username, M.stage, SUM(S.points) AS totalPoints

FROM team_members AS M

LEFT JOIN team_member_scores AS S ON M.username = S.username

WHERE M.team_id = $team_id 

GROUP BY M.username, M.stage

ORDER BY points DESC LIMIT 5";

And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that [icode]AND M.game_id = ...[/code] here. Note also that both fields are needed in the GROUP BY, as shown.)

Thanks again! Yeah, I changed it from member_name to username just to show that they were referring to the same info, but forgot to mention that much needed fact when I posted. Sorry about that. :o

Again, the info you posted is gold to me! :)