I have following data in tourament MySQL table.
id | name | team |
---|---|---|
1 | Warren | A |
2 | Carol | B |
3 | Donna | |
4 | William | A |
5 | Andrew | C |
6 | Neil | A |
7 | Max | A |
8 | Phil | |
9 | William | |
10 | Keith | |
11 | Phil |
I am trying to create a query to output the data in the following form to process it further.
name | team |
---|---|
A | 1 |
B | 1 |
C | 1 |
Donna | 0 |
Keith | 0 |
Phil | 0 |
Phil | 0 |
William | 0 |
Please have a look here for data.
Current attempt:
select COALESCE(NULLIF(`team`,''), `name`) as name,
case
when `team` = '' THEN false
when `team` != '' THEN true
end as `group`
from `tournament` order by COALESCE(NULLIF(`team`, ''), `name`) ASC;
3
Answers
Your attempt is good, you just need to rewrite
COALESCE(NULLIF(team,''), name)
toCOALESCE(team, name)
becauseCOALESCE
will only move on to the next parameter if the current parameter isNULL
. Since'' != NULL
thename
column will never be selected.Something like:
Your approach is good!
Try the below code to achieve the expected output:
It works also without union like this
result:
click here for a sample