CODEWITHSUNDEEP
mysqlsqlpostgresqljoin
Jacob
Jacob

Reputation: 4031

Postgresql merge same columns from the JOIN

I formed the question a bit silly. But let me express m problem on a example I Am struggling with.

My query gives me the following result below: enter image description here

As you can see, everything is the same except the last column. How can I join the results into one row with both values for the last column.

My query: 

select tp.tp_vorname as PatientFirstName, tp.tp_nachname as PatientLastName,
       tp.tp_geburtsdatum as patientBirthday, pt.pt_id, te.te_startzeit as StartTime, 
       te.te_endzeit As EndTime, te_datecreated as DateCreated, tpw.li_id_warnungen as Warning
from termin_patient tp
INNER JOIN patienten_termin pt
    on tp.tp_id = pt.tp_id
INNER JOIN termin te
    on te.te_id = pt.pt_id
LEFT JOIN terminpatient_warnungen tpw
on tp.tp_id = tpw.tp_id
where pt.pt_id = te.te_id and tp.tp_id=91168 and 
      te.te_startzeit >= '2017-05-24' 
  AND te.te_startzeit <  '2017-06-02'

Upvotes: 1

Views: 106

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

You can use GROUP BY on all SELECT columns (but not on the last - tpw.li_id_warnungen) and aggregate the last column into one field.

Solution for PostgreSQL:

You can use the following solution using GROUP BY and STRING_AGG. To use the column tpw.li_id_warnungen on STRING_AGG you have to CAST the value to TEXT. Another solution instead of using CAST could be to use the direct cast tpw.li_id_warnungen::text on the column.

SELECT 
    tp.tp_vorname AS PatientFirstName, 
    tp.tp_nachname AS PatientLastName,
    tp.tp_geburtsdatum AS patientBirthday, 
    pt.pt_id, 
    te.te_startzeit AS StartTime, 
    te.te_endzeit AS EndTime, 
    te_datecreated AS DateCreated, 
    STRING_AGG(CAST(tpw.li_id_warnungen AS TEXT), ',') AS Warning
FROM 
    termin_patient tp INNER JOIN patienten_termin pt ON tp.tp_id = pt.tp_id
    INNER JOIN termin te ON te.te_id = pt.pt_id
    LEFT JOIN terminpatient_warnungen tpw ON tp.tp_id = tpw.tp_id
WHERE 
    pt.pt_id = te.te_id 
    AND tp.tp_id = 91168 
    AND te.te_startzeit >= '2017-05-24' 
    AND te.te_startzeit < '2017-06-02' 
GROUP BY 
    tp.tp_vorname, 
    tp.tp_nachname, 
    tp.tp_geburtsdatum, 
    pt.pt_id, 
    te.te_endzeit, 
    te_datecreated

With the additional warning names (described in comments) your query would look like this (no need for CAST or direct cast anymore):

SELECT 
    tp.tp_vorname AS PatientFirstName, 
    tp.tp_nachname AS PatientLastName,
    tp.tp_geburtsdatum AS patientBirthday, 
    pt.pt_id, 
    te.te_startzeit AS StartTime, 
    te.te_endzeit AS EndTime, 
    te_datecreated AS DateCreated, 
    STRING_AGG(lip.li_name, ',') AS Warning
FROM 
    termin_patient tp INNER JOIN patienten_termin pt ON tp.tp_id = pt.tp_id
    INNER JOIN termin te ON te.te_id = pt.pt_id
    LEFT JOIN terminpatient_warnungen tpw ON tp.tp_id = tpw.tp_id 
    LEFT JOIN li_patientenwarnung lip ON tpw.li_id_warnungen = lip.li_id
WHERE 
    pt.pt_id = te.te_id 
    AND tp.tp_id = 91168 
    AND te.te_startzeit >= '2017-05-24' 
    AND te.te_startzeit < '2017-06-02' 
GROUP BY 
    tp.tp_vorname, 
    tp.tp_nachname, 
    tp.tp_geburtsdatum, 
    pt.pt_id, 
    te.te_endzeit, 
    te_datecreated

Solution for MySQL:

You can use the following solution on MySQL using GROUP BY and GROUP_CONCAT:

SELECT 
    tp.tp_vorname AS PatientFirstName, 
    tp.tp_nachname AS PatientLastName,
    tp.tp_geburtsdatum AS patientBirthday, 
    pt.pt_id, 
    te.te_startzeit AS StartTime, 
    te.te_endzeit AS EndTime, 
    te_datecreated AS DateCreated, 
    GROUP_CONCAT(tpw.li_id_warnungen) as Warning
FROM 
    termin_patient tp INNER JOIN patienten_termin pt ON tp.tp_id = pt.tp_id
    INNER JOIN termin te ON te.te_id = pt.pt_id
    LEFT JOIN terminpatient_warnungen tpw ON tp.tp_id = tpw.tp_id
    LEFT JOIN li_patientenwarnung lip ON tpw.li_id_warnungen = lip.li_id
WHERE 
    pt.pt_id = te.te_id 
    AND tp.tp_id=91168 
    AND te.te_startzeit >= '2017-05-24' 
    AND te.te_startzeit <  '2017-06-02' 
GROUP BY 
    tp.tp_vorname,
    tp.tp_nachname, 
    tp.tp_geburtsdatum,
    pt.pt_id,
    te.te_endzeit,
    te_datecreated

With the additional warning names (described in comments) your query would look like this:

SELECT 
    tp.tp_vorname AS PatientFirstName, 
    tp.tp_nachname AS PatientLastName,
    tp.tp_geburtsdatum AS patientBirthday, 
    pt.pt_id, 
    te.te_startzeit AS StartTime, 
    te.te_endzeit AS EndTime, 
    te_datecreated AS DateCreated, 
    GROUP_CONCAT(lip.li_name) as Warning
FROM 
    termin_patient tp INNER JOIN patienten_termin pt ON tp.tp_id = pt.tp_id
    INNER JOIN termin te ON te.te_id = pt.pt_id
    LEFT JOIN terminpatient_warnungen tpw ON tp.tp_id = tpw.tp_id
WHERE 
    pt.pt_id = te.te_id 
    AND tp.tp_id=91168 
    AND te.te_startzeit >= '2017-05-24' 
    AND te.te_startzeit <  '2017-06-02' 
GROUP BY 
    tp.tp_vorname,
    tp.tp_nachname, 
    tp.tp_geburtsdatum,
    pt.pt_id,
    te.te_endzeit,
    te_datecreated

Upvotes: 1

Related Questions