tournament query

[Destramic]

hey guys im trying to produce a tournament bracket and im having a problem with the query below...

 

basically when a tournament is created you can select single or team

 

if single then get the player id and match to a player

if team then match team to player_id

 

if someone could help me on why the query is returning errors

 

SELECT t.tournament_id,
       t.game_id, 
       t.tournament_name, 
       t.tournament_type, 
       t.tournament_size,

CASE t.tournament_type
WHEN 'Single'
THEN

ptm.player_id,
ptm.starting_position
p.player_name,
u.nationality,
FROM tournaments t
LEFT JOIN player_tournament_mappings ptm ON ptm.tournament_id = t.tournament_id
LEFT JOIN players p ON p.player_id = ptm.player_id
LEFT JOIN user_player_mappings upm ON upm.player_id = ptm.player_id
LEFT JOIN users u ON u.user_id = upm.player_id

WHEN 'Team'
THEN



END
WHERE t.tournament_id = 1

 

thank you

[awjudd]

Destramic - didn't I already tell you this on IRC a week or two back that you can't conditionally join on stuff depending on stuff in your CASE statement?

 

You need to either UNION the two queries together (i.e. one for Team tournaments and one for Singles tournaments) or alter your table structures so that they are all held in the same table (all tournaments).

 

~juddster

[Destramic]

yeah you did sorry judda...ok well ive been trying to work how to merge both together on what you've said and at the moment ive only dont team and single tounranaments singlerly

 

hear you'll find a query for single tounrnaments then team tournaments...if you can explain or help me on how i can merge to create one query which having a where torunament_type = 'Single' / 'Team' and it just selects from a possible id

 

thanks for your help judda

 

SELECT t.tournament_id,
       t.game_id, 
       t.tournament_name, 
       t.tournament_type, 
       t.tournament_size,
       ptm.player_id,
       ptm.starting_position,
       p.player_name,
       u.user_nationality
FROM tournaments t
LEFT JOIN player_tournament_mappings ptm ON ptm.tournament_id = t.tournament_id
LEFT JOIN players p ON p.player_id = ptm.player_id
LEFT JOIN user_player_mappings upm ON upm.player_id = ptm.player_id
LEFT JOIN users u ON u.user_id = upm.player_id
WHERE t.tournament_Type = 'Single' AND
t.tournament_id = 1


SELECT t.tournament_id,
       t.game_id, 
       t.tournament_name, 
       t.tournament_type, 
       t.tournament_size,
       ptm.player_id,
       ptm.starting_position,
       tm.team_name,
       tm.team_nationality
FROM tournaments t
LEFT JOIN player_tournament_mappings ptm ON ptm.tournament_id = t.tournament_id
LEFT JOIN player_team_mappings ptm2 ON ptm2.player_id = ptm.player_id
LEFT JOIN teams tm ON tm.team_id = ptm2.team_id
WHERE t.tournament_Type = 'Team' AND
t.tournament_id = 2