How could I use case within join and union?

I have 2 tables:

Matches

CREATE TABLE rounds (
  `roundid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `roundname` VARCHAR(45) NULL);

Rounds

CREATE TABLE matches (
  `matchid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `matchwinner` VARCHAR(45) NULL,
  `roundid` INT NOT NULL,
  CONSTRAINT `fk_matches_rounds`
    FOREIGN KEY (`roundid`)
    REFERENCES `mydb`.`rounds` (`roundid`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

Inserts

INSERT INTO rounds (`roundid`, `roundname`) VALUES (1, 'Final');
INSERT INTO rounds (`roundid`, `roundname`) VALUES (2, '3rd place');
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (1, 'fnatic', 1);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (2, 'astralis', 1);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (3, 'fnatic', 2);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (4, 'fnatic', 2);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (5, 'astralis', 1);
INSERT INTO matches (`matchid`, `matchwinner`, `roundid`) VALUES (6, 'astralis', 1);

My query:

SELECT 
    m.matchid,
    m.matchwinner Team,
    CASE r.roundname
        WHEN r.roundname = 'Final' THEN '1st'
        ELSE '2'
    END Place
FROM
    matches m
        JOIN rounds r ON m.roundid = r.roundid
WHERE
    r.roundname = 'Final' 
UNION SELECT 
    m.matchid,
    m.matchwinner Team,
    CASE r.roundname
        WHEN r.roundname = '3rd place' THEN '3rd'
        ELSE '4th'
    END Place
FROM
    matches m
        JOIN rounds r ON m.roundid = r.roundid
WHERE
    r.roundname = '3rd place';

I would like to know

• what places a particular team achieved (should probably use HAVING m.matchwinner = 'fnatic')

MatchID – Team – Place
1 – fnatic – 1st
3 – fnatic – 3rd
4 – fnatic – 3rd

• what places all teams achieved

MatchID – Team – Place
1 – fnatic – 1st
2 – astralis – 1st
3 – fnatic – 3rd
4 – fnatic – 3rd
and so on

Don’t understand why my query isn’t working?

Answer

This looks like a join and conditional logic:

select 
    m.matchid,
    m.matchwinner team,
    case r.roundname
        when 'Final'     then '1st'
        when '3rd place' then '3rd'
    end place
from rounds r
inner join matches m on m.roundid = r.roundid
where m.matchwinner = 'fnatic' 
order by m.matchid

To get the same result for all teams, just remove the where clause.

Demo on DB Fiddle:

matchid | team   | place
------: | :----- | :----
      1 | fnatic | 1st  
      3 | fnatic | 3rd  
      4 | fnatic | 3rd