Mateusz Urbański Mateusz Urbański - 29 days ago 15
SQL Question

Combine three columns in to one

I have the following sql statement:

SELECT p.id, p.first_name, p.last_name, a.admitted_at,
a.discharged_at, c.name as client_name, p.receive_reminders,
p.opt_in_to_receive_reminders, u.phone_number, p.status,
c.id as client_id, p.mrn
FROM patients p
LEFT JOIN admissions a ON p.last_admission_id = a.id
INNER JOIN facilities f ON f.id = p.facility_id
INNER JOIN clients c ON c.id = f.client_id
LEFT OUTER JOIN users u ON p.user_id = u.id;


I have three columns:

p.opt_in_to_receive_reminders - boolean
u.phone_number - string
p.receive_reminders - boolean


I want to combine result of this 3 columns in to one virtual column -
messaging
. For example if
p.opt_in_to_receive_reminders
is true and if
u.phone_number
is not empty and
p.receive_reminders
is true then this column will have true value, in other scenario it will return false.

How can I do this in PostgreSQL?

Answer

Just write this:

p.opt_in_to_receive_reminders is true and if u.phone_number is not empty and p.receive_reminders is true

as

select p.opt_in_to_receive_reminders and u.phone_number is not null and p.receive_reminders
-- or
select p.opt_in_to_receive_reminders and u.phone_number <> '' and p.receive_reminders
-- or
select p.opt_in_to_receive_reminders and coalesce(u.phone_number, '') is null and p.receive_reminders