I have two queries. The first -
SELECT
communications.creation_date as message_date,
message as message_text,
employees.first_name || ' ' || coalesce(employees.middle_name,'') || ' ' || employees.last_name as message_by
FROM app.communications
INNER JOIN app.employees ON communications.message_from = employees.emp_id
WHERE send_as_sms = TRUE AND com_id = (SELECT MAX(com_id) FROM app.communications)
which basically outputs - | message_date | message_text | message_by |
And the second query -
SELECT
cs.com_id,
cs.first_name ||' ' || cs.last_name AS recipient_name,
cs.sim_number AS phone_number
FROM app.communication_sms cs
WHERE cs.com_id = (SELECT MAX(cs2.com_id) FROM app.communication_sms cs2)
ORDER BY first_name ASC
which outputs - | com_id | recipient_name | phone_number |
As you can tell from the queries, both tables have a "com_id"
column. What I need is to make a single query that will merge the two queries above to get a single output, something like -
|message_date|message_text|message_by|recipient_name|phone_number|
How can I achieve that? I can't use UNION
because of the different data types and different number of columns. I'll appreciate your help guys.
Not sure if the com_id will be equal or not, but in case they might not be then I suggest this:
select * -- list out the columns, I haven't bothered here
FROM (
SELECT MAX(com_id) as com_id FROM app.communications
UNION
SELECT MAX(cs2.com_id) FROM app.communication_sms cs2
) u
left join (
SELECT
com_id -- don't know which table this comes from
communications.creation_date as message_date,
message as message_text,
employees.first_name || ' ' || coalesce(employees.middle_name,'') || ' ' || employees.last_name as message_by
FROM app.communications
INNER JOIN app.employees ON communications.message_from = employees.emp_id
WHERE send_as_sms = TRUE AND com_id = (SELECT MAX(com_id) FROM app.communications)
) s1 on u.com_id = s1.com_id
left join (
SELECT
cs.com_id,
cs.first_name ||' ' || cs.last_name AS recipient_name,
cs.sim_number AS phone_number
FROM app.communication_sms cs
WHERE cs.com_id = (SELECT MAX(cs2.com_id) FROM app.communication_sms cs2)
ORDER BY first_name ASC
) s2 on u.com_id = s2.com_id
Note a small amount of repetition could be avoided by using CTEs