How to return different result in the same query?

I'm trying to return different result in one query, in particular the query return the ranking of a particular competition using round.id field, now sometimes this table can have the field group.id valorized, if so I need to return only the ranking which have as group.id the minimum value available, so I create this design:

SELECT l.*, t.name as team_name, r.name AS rank_name, r.color AS rank_color FROM league_ranking l LEFT JOIN team t ON l.team_id = t.id LEFT JOIN competition_ranks r ON l.rank = r.id INNER JOIN competition_groups g  WHERE l.round_id = :round_id AND l.group_id = (   SELECT MIN(l2.group_id)   FROM league_ranking l2   WHERE l2.round_id = :round_id ) 

this working if the ranking records have the group.id available, but if this field is NULL nothing will be returned, a little example of league_ranking table data:

| round_id | group_id | team_id      5         3         1045      5         3         1046      6         NULL      1047      6         NULL      1048 

if I search as round.id 5, will be returned the first two records, but if instead I search for round.id 6, nothing will be returned. How can I structure my query to return the result also if there is no group.id associated?

1 Answers

Answers 1

null isn't a value, it's the lackthereof. null = null returns null, not true, so for groups without an id, this query won't work.

You can use the <=> instead of =, though, to evaluate two nulls as being equal:

SELECT l.*, t.name as team_name, r.name AS rank_name, r.color AS rank_color FROM league_ranking l LEFT JOIN team t ON l.team_id = t.id LEFT JOIN competition_ranks r ON l.rank = r.id INNER JOIN competition_groups g  WHERE l.round_id = :round_id AND l.group_id <=> ( -- <=> used here instead of =   SELECT MIN(l2.group_id)   FROM league_ranking l2   WHERE l2.round_id = :round_id ) 

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati