CODEWITHSUNDEEP
mysqlsqldatabase
Bernard Polman
Bernard Polman

Reputation: 855

Get value from other table if condition is not met

I'm writing an SQL query:

DECLARE @ResidentID AS INT = NULL
DECLARE @Category AS INT = NULL
DECLARE @ResidentStatus AS INT = NULL
DECLARE @Date AS DATETIME = NULL--'2018-02-27'

SELECT DISTINCT r.Firstname, r.Lastname, r.Active FROM Register.Resident r
LEFT JOIN Register.MonitoringStatusOfUser msu ON msu.ResidentID = r.ID
WHERE (r.ID = @ResidentID OR @ResidentID IS NULL)

Each resident from Register.Resident 'r' has a field called 'Status' which is an integer. In table Register.MonitoringStatusOfUser I have records of statuses for each user, sort of like a history of status changes. What I want to do is: check if there is a record for a user in Register.MonitoringStatusOfUser 'msu'. If @Date is between msu.DateFrom and msu.DateTo, then display msu.Status. If not, then display r.Status.

I've already made a left join for Register.MonitoringStatusOfUser. However, I can't figure out how to display r.Status if the @Date condition I've explained above isn't met.

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I'm not sure if the DISTINCT is needed. Add it back in if you need it.

You can put the date condition in the ON clause. Then use COALESCE() to get the status you want:

SELECT r.Firstname, r.Lastname, r.Active,
       COALESCE(msu.status, r.status) as status
FROM Register.Resident r LEFT JOIN
     Register.MonitoringStatusOfUser msu
     ON msu.ResidentID = r.ID AND
        @Date BETWEEN msu.DateFrom AND msu.DateTo
WHERE (r.ID = @ResidentID OR @ResidentID IS NULL)

Upvotes: 1

Related Questions