CODEWITHSUNDEEP
mysqlcase
C. Ovidiu
C. Ovidiu

Reputation: 1134

MySQL, make CASE() return one result only, the one matched

I have two tables:

agents(id)
agent_targets(agent_id, month, amount)

For each month I have a month field, so for example for the month June 2017 the field is 2017-05-01.

Now, when I get all agents I also need to get their monthly targets, for the current month-year.

My query is as follow (simplified)

SELECT 
(CASE
    WHEN MONTH(targets.month) = MONTH(CURRENT_DATE()) AND YEAR(targets.month) = YEAR(CURRENT_DATE())
    THEN targets.amount
END) as monthly_target
FROM agent_targets targets

This returns what I expect, the problem is that is return multiple rows, of which all but one are NULL because it didn't match the WHEN, and one that return the amount I need.

But because I am doing this inside a bigger query when getting all agents, I only need the record that matched the WHEN, instead I always get a NULL result because it's the first row in the table.

Is there any way I can make the CASE 'stop' once the WHEN condition is met, and return only that result ?

Note that I cannot GROUP BY agent_id because there is already grouping in the original query.

Thank you

EDIT/SOLVED Ok, so after a bit of trying I managed to make it work. Instead of using the CASE() I simply moved the whole condition in the JOIN statement, something like this

[...]
LEFT JOIN agent_targets targets ON agents.id = targets.agent_id AND MONTH(targets.month) = MONTH(CURRENT_DATE()) AND YEAR(targets.month) = YEAR(CURRENT_DATE())
[...]

Upvotes: 1

Views: 54

Answers (1)

ErikusMaximus
ErikusMaximus

Reputation: 1218

You should use a WHERE clause instead of CASE.

Upvotes: 1

Related Questions