SQL trying to use a column from a joined table in a subquery

Here is what I currently have which returns 3 columns for patient_id, group_concat_1, and group_concat_2:

SELECT patient_id,
(SELECT GROUP_CONCAT(column1) FROM 
table1 where patient_id = patient.id
) group_concat_1,
(SELECT GROUP_CONCAT(column1) FROM 
table2 where patient_id = patient.id
) group_concat_2
FROM patient

However, I need to return a single column with group_concat_1 and group_concat_2 combined, so I tried this:

SELECT patient_id,
SELECT CONCAT(group_concat_1, group_concat_2) FROM (
    (SELECT GROUP_CONCAT(column1) FROM 
    table1 where patient_id = patient.id
    ) group_concat_1,
    (SELECT GROUP_CONCAT(column1) FROM 
    table2 where patient_id = patient.id
    ) group_concat_2 
)
FROM patient

But his clearly doesn’t work since now it can’t find patient.id in the inner subquery. Any advice? Thanks!

Answer

You can concatenate directly the 2 columns:

SELECT p.patient_id,
  CONCAT(  
    (SELECT GROUP_CONCAT(column1) FROM table1 where patient_id = p.patient.id),
    (SELECT GROUP_CONCAT(column1) FROM table2 where patient_id = p.patient.id)
  )
FROM patient p