CODEWITHSUNDEEP
mysqlsql
Charanoglu
Charanoglu

Reputation: 1339

How to use an if condition in a query?

Suppose I want select the matches with the MAX gameweek if the status of all matches is setted to 3, or the matches with MIN gameweek how can I do this?

Data sample

id | status | gameweek | round_id 
 1     3         3          1
 2     3         3          1
 3     1         3          1
 4     1         4          1
 5     1         4          1
 6     1         4          1

the final result should be: 1, 2, 3 because not all the match has played. I was able to setup a query for MAX:

SELECT MAX(gameweek) FROM `match` m WHERE round_id = 1 AND m.status = 3

but I have some difficult to implement an IF condition, someone could help me?

Thanks.

UPDATE

In the solution proposed by the users I noticed that I doesn't explain well a particular situation: if all the matches of a round doesn't have the status 3, the query should return the MIN(gameweek) of the round_id specified.

Upvotes: 0

Views: 79

Answers (3)

Charanoglu
Charanoglu

Reputation: 1339

ok, simpler than I imagined, I discovered that I can achieve this target using the COALESCE operator, so:

m.gameweek = (SELECT COALESCE(MIN(
                    CASE WHEN m2.status < 3 
                         THEN m2.gameweek END), 
                   MAX(m2.gameweek))
                   FROM `match` m2 
                   WHERE m2.round_id = m.round_id)

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

If I get this right, you could use a CASE. If a record with a status other than 3 exists return the minumum gameweek, else the maximum. Compare the result to the gameweek.

SELECT `m1`.*
       FROM `match` `m1`
       WHERE `m1`.`round_id` = 1
             AND `m1`.`gameweek` = CASE
                                     WHEN EXISTS (SELECT *
                                                         FROM `match` `m2`
                                                         WHERE `m2`.`round_id` = `m1`.`round_id`
                                                               AND `m2`.`status` <> 3) THEN
                                       (SELECT min(`m3`.`gameweek`)
                                               FROM `match` `m3`
                                               WHERE `m3`.`round_id` = `m1`.`round_id`)
                                     ELSE
                                       (SELECT max(`m4`.`gameweek`)
                                               FROM `match` `m4`
                                               WHERE `m4`.`round_id` = `m1`.`round_id`)
                                   END;

I'm not sure if you wanted to limit it to a certain round_id. It's in your query but not in the text. If you don't want it, remove all the conditions related to round_id.

Edit:

To use the maximum gameweek if not all status of a round are equal to 3 and the minimum gameweek otherwise you can check if the minimum of the status equals the maximum (i.e. all status are the same) and if it is equal to 3.

SELECT `m1`.*
       FROM `match` `m1`
       WHERE `m1`.`round_id` = 1
             AND `m1`.`gameweek` = CASE
                                     WHEN EXISTS (SELECT min(`m2`.`status`)
                                                         FROM `match` `m2`
                                                         WHERE `m2`.`round_id` = `m1`.`round_id`
                                                         HAVING min(`m2`.`status`) <> max(`m2`.`status`)
                                                                OR min(`m2`.`status`) <> 3) THEN
                                       (SELECT min(`m3`.`gameweek`)
                                               FROM `match` `m3`
                                               WHERE `m3`.`round_id` = `m1`.`round_id`)
                                     ELSE
                                       (SELECT max(`m4`.`gameweek`)
                                               FROM `match` `m4`
                                               WHERE `m4`.`round_id` = `m1`.`round_id`)
                                   END;

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

Assuming there can be more than one round_id:

SELECT *
FROM `match` m 
WHERE (gameweek, round_id ) = 
(
  SELECT MAX(gameweek),round_id 
  FROM `match` m 
  WHERE round_id = 1 AND m.status = 3
  GROUP BY round_id
)

DBFiddle DEMO

Upvotes: 1

Related Questions