Is there a better/more concise way to write this sql query?

[Supplement]

Instead of using 'case' as many times as i do?

select
case
when b.patient_id is null then a.dfcode else b.dfcode
end as dfcode,
case
when b.patient_id is null then a.diagcode else b.diagnosis
end as diasgnosis,
case
when b.patient_id is null then a.times_testing else b.times_testing
end as times_testing,
case
when b.patient_id is null then a.on_insulin else b.insulin
end as insulin
from event.dbo.patient_dg() a
left outer join event.dbo.diabetes_rx_tracker b on a.code=b.patient_id and b.image_id='2110180'
where a.code='ADSMANZS01'

[NogDog]

Looks like IFNULL() could contribute to some conciseness.

[gk53]

if you on SQL server this will work

select ISNULL(a.dfcode, b.dfcode) as dfcode,
ISNULL(a.diagcode, b.diagcode) as diagcode,
ISNULL(a.times_testing, b.times_testing) as times_testing,
ISNULL(a.on_insulin, b.on_insulin) as on_insulin,
from event.dbo.patient_dg() a
left outer join event.dbo.diabetes_rx_tracker b on a.code=b.patient_id and b.image_id='2110180'
where a.code='ADSMANZS01'

[Supplement]

that looks excellent...

i've never used the ISNULL function.. i think it will work a treat!


=)

[Supplement]

I have another question regarding duplicates and inserting...

if first name last name and phone number do not exist... than insert... if they do exist.. do nothing... here's what i have so far...


$exists = sql::value("Select 1 from event.aps.trainer where first = $first, last = $last, trainer_phone = $trainer_phone");
if (!$exists) sql::safe_insert('event', 'aps', 'trainer', $trainer_hex);
else
{
IGNORE ('event', 'aps', 'trainer', $trainer_hex);
}