mysql - Multiple joins with multiple conditions with multiple tables -

at first tables:

game +----+--------------+ | id | game         | +----+--------------+ |  1 | game1        | |  2 | game2        | |  4 | game4        | +----+--------------+  group_game +---------+----------+ | game_id | group_id | +---------+----------+ |       1 | 33       | |       1 | 45       | |       4 | 33       | +---------+----------+  groups +----+------------+---- | id | group_name | ... +----+------------+---- | 33 | group33    | ... | 45 | group45    | ... +----+------------+----  users +---------+----------+---- | user_id | username | ... +---------+----------+---- |       1 | user1    | ... |       2 | user2    | ... +---------+----------+----  users_groups +---------+----------+ | user_id | group_id | +---------+----------+ |       1 | 33       | |       1 | 45       | |       2 | 45       | +---------+----------+ 

what want do

now want check wether current user in group plays "game4" , if yes output should id , name of group.

• the current user "user1" id 1 (table users)
• "user1" in group id 33 (table users_groups)
• the group-id 33 belongs "group33" (table groups)
• the group id 33 plays game id 4 (table group_game)
• the game id belongs game "game4" (table game)
• conclusion: yes, user in group plays game4, output name of group ("group33")

my current code (which gives me no rows)

$user_id = $_session["user_id"]; $game4= "game4";  $gruppen_dayz = $db->prepare("     select g.group_id, g.group_name     groups g      left join users_groups ug         on g.group_id = ug.group_id     left join group_game gg         on g.group_id = gg.group_id     left join game ga         on ga.id = gg.game_id     ga.game = ? , ug.user_id = ? "); $gruppen_dayz->bind_param('ii', $game4, $user_id); 

i don't know how should build query :/

your clause being applied after joins generate dataset, negating left join aspect of joins. solve move criteria join limits applied before joins. otherwise, null values generated in left joins excluded clause.

there may other elements well, first component saw.

$user_id = $_session["user_id"]; $game4= "game4";  $gruppen_dayz = $db->prepare("     select g.group_id, g.group_name     groups g      left join users_groups ug         on g.group_id = ug.group_id         , ug.user_id = ?     left join group_game gg         on g.group_id = gg.group_id     left join game ga         on ga.id = gg.game_id        , ga.game = ?  "); $gruppen_dayz->bind_param('ii', $game4, $user_id); 

---update ---- upon further investigation believe joins wrong. groups doesn't have group_id field according table structure. walking though rest now...

select g.group_id, g.group_name groups g  left join users_groups ug     on g.id = ug.group_id left join group_game gg     on g.id = gg.group_id left join game ga     on ga.id = gg.game_id ga.game = ? , ug.user_id = ? 

i might rewrite as...

select g.id, g.group_name users_groups ug inner join groups g  on ug.group_id = g.id inner join group_game gg  on gg.group_id = g.id inner join game ga  on ga.id = gg.game_id ga.game = ? , ug.user_id = ? 

i see no value or need left joins based on criteria.