Replace Mysql null field with a custom string

Question

I am working on an application build on Laravel on the back in with Mysql. I have the below Mysql query doest quite work as expected yet. I am not a Mysql expert but basically what I am trying to do is for every AptStatus = 3 I have a Null field for Operatory. Id like to convert that Null field to the Operatory name for each Operatory in the location. (hopefully that makes sense).

I am storing this in php as a variable to store my list of locations.

$locs = (below query in Eloquent)

Locations query:

select
    op.Location, op.Operatory 
from rpt_officeschedules as op 
left join apptwidget_locations al on op.Location = al.location
where 
    (op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%') and
    al.isOfficeClosed = 0
group by Location, Operatory;

Query Results:

Location | Operatory | 
---------  ----------
Addison.    2 NP
Addison.    3 NP/TX

MySql Query:

select
    Location,
    AptDate,
    IF(AptStatus = 3, '08:00:00', AptTime) AptTime,
    IF(AptStatus = 3, 540, AptLength) AptLength,
    AptStatus,
    OperatoryNum,
    Operatory
from rpt_officeschedules
where
    AptTime between '07:59:00' and '17:30:00' and 
    AptDate between '2020-11-07' and '2020-11-08' and 
    (
        Operatory LIKE '%NP%' or 
        Operatory LIKE '%OPEN%' or 
        Operatory is null
    )
order by Location, AptDate, OperatoryNum, AptTime, Operatory;

Query results

Location | AptDate   | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- ---------   --------- ----------- ----------- ----------- --------------
Addison.  2020-11-07   08:00:00.  540        null            3           0
Addison   2020-11-07   08:00:00.  540        null            3           0
Addison.  2020-11-08   08:00:00.  540        null            3           0
Addison   2020-11-08   08:00:00.  540        null            3           0

What id like to do is when a location shows an AptStatus = 3, to convert that null field for each Operatory name ($locs)

Expected results:

Location | AptDate   | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- ---------   --------- ----------- ----------- ----------- --------------
Addison.  2020-11-07   08:00:00.  540        2 NP            3           0
Addison   2020-11-07   08:00:00.  540        3 NP/TX         3           0
Addison.  2020-11-08   08:00:00.  540        2 NP            3           0
Addison   2020-11-08   08:00:00.  540        3 NP/TX         3           0

Any Ideas?

user_984106 · Answer

Something like this if I've got your inputs correctly:

select
    os.Location,
    os.AptDate,
    IF(os.AptStatus = 3, '08:00:00', os.AptTime) AptTime,
    IF(os.AptStatus = 3, 540, os.AptLength) AptLength,
    os.AptStatus,
    os.OperatoryNum,
    COALESCE(os.Operatory, op.Operatory)
from rpt_officeschedules os
left join apptwidget_locations al on al.location = os.Location and al.isOfficeClosed = 0
left join rpt_officeschedules op on op.Location = os.Location and (op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%')
where
    os.AptTime between '07:59:00' and '17:30:00' and 
    os.AptDate between '2020-11-07' and '2020-11-08' and 
    (
        os.Operatory LIKE '%NP%' or 
        os.Operatory LIKE '%OPEN%' or 
        os.Operatory is null
    )
order by os.Location, os.AptDate, os.OperatoryNum, os.AptTime, COALESCE(os.Operatory, op.Operatory);

Replace Mysql null field with a custom string

1 Answers