sqlpostgresql-9.1multi-query

Merge two SELECT statements with different data types and number of columns into one output in PostgreSQL


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.


Solution

  • 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