Mysql query help, joining multiple tables. (Solved)

[eluser]goliatone[/eluser]
Hi there,
I'm using Simon Stenhouse's DataMapper library.

So i have created the tables in order to use the library, and everything works great but I now need a SQL query to export an Excel from phpmyadmin.


I have the following tables:
games
games_matches
matches
matches_players
players


I need to retrieve all players related to a game.


My query atm looks like this:

Code:

$sql = ' SELECT `players`. *'
        . ' FROM ('
        . ' `players` , `games`'
        . ' )'
        . ' LEFT JOIN `matches_players` ON `players`.`id` = `matches_players`.`player_id`'
        . ' LEFT JOIN `games_matches` ON `games_matches`.`game_id` = 2'
        . ' LEFT JOIN `matches` ON `matches`.`id` = `matches_players`.`match_id`'
        . ' WHERE `games_matches`.`game_id` = 2'
        . ' GROUP BY players.id'
        . ' ORDER BY `players`.`name` ASC'
        . ' LIMIT 0 , 30 ';

Obviously it's not working.

Any advice would greatly be appreciated!

regards,
goliatone

[eluser]@rno[/eluser]
Hi goliatone,

Shouldn't your SQL look like this:

Code:

$sql = ' SELECT `players`.*'
        . ' FROM `players`'
        . ' LEFT JOIN `matches_players` ON `players`.`id` = `matches_players`.`player_id`'
        . ' LEFT JOIN `games_matches` ON `games_matches`.`game_id`'
        . ' LEFT JOIN `matches` ON `matches`.`id` = `matches_players`.`match_id`'
        . ' WHERE `games_matches`.`game_id` = 2'
        . ' GROUP BY players.id'
        . ' ORDER BY `players`.`name` ASC'
        . ' LIMIT 0 , 30 ';

Good luck!

Arno

[eluser]goliatone[/eluser]
Thanks for the reply.

The query as it is will return all players , and not only the ones related to a particular game id.

ps: I did try your query with no luck, thnxs anyway.

[eluser]@rno[/eluser]
But what is it exactly you want to query?
Do you need all players or do you need all players of a certain match or game?

Regards,
Arno

[eluser]goliatone[/eluser]
Sorry if I was not clear about it.

I need all players related to a certain game id.


The logic would be:
given a game id, pull all matches from games_matches that have games_matches.game_id == id.
Having those matches, pull all players that where matches_players.player_id = matches.id.


regards,
goliatone

[eluser]@rno[/eluser]
Aha, I see. I was making a little mistake in my previous SQL statement.
I think this is what is shoud look like:

Code:

$sql = ' SELECT `players`.*'
        . ' FROM `players`'
        . ' LEFT JOIN `matches_players` ON `players`.`id` = `matches_players`.`player_id`'
        . ' LEFT JOIN `matches` ON `matches`.`id` = `matches_players`.`match_id`'
        . ' LEFT JOIN `games_matches` ON `games_matches`.`match_id` = `matches`.`id`'
        . ' WHERE `games_matches`.`game_id` = 2'
        . ' GROUP BY players.id'
        . ' ORDER BY `players`.`name` ASC'
        . ' LIMIT 0 , 30 ';

Hope this helps

Regards,
Arno

[eluser]goliatone[/eluser]
Ok Arno, thanks a lot for the help.
That last query you provided did work.

I see where i was getting wrong. Makes sense.


regards,
goliatone

[eluser]@rno[/eluser]
Glad I could help!

Good luck

Arno