unable to join on a sub query

Question

apologies for the question name I was unsure how to describe this problem :

take the following CTE

WITH cte_form_answers AS (
SELECT fa.id
,      MAX(CASE WHEN fa.question = 'contact' THEN fa.answer END) AS ContactMethod
FROM formanswers fa
GROUP BY fa.id)

---

SELECT * FROM cte_form_answers 
id | ContactMethod
0  | Mobile
1  | Landline

and the following table

SELECT id, ContactMethod, Contact from contacts
id | ContactMethod | Contact
0  | Mobile        | xxxx-xxx-xxx
0  | Email         | [email protected]
1  | Landline      | xxxx-xxxx-xxx
1  | Mobile        | xxx-xxx-xxxx

I'm attempting to join using the contatMethod from my within my CTE onto the contact table

My own attempt has been :

WITH cte_form_answers AS (SELECT fa.id
,      MAX(CASE WHEN fa.question = 'contact' THEN fa.answer END) AS ContactMethod
FROM formanswers fa
LEFT JOIN contacts c 
ON   c.id = fa.id 
AND  c.ContactMethod = ( SELECT fa1.id, MAX(CASE WHEN fa1.question = 'contact' THEN fa1.answer END) 
                         FROM formanswers fa1 GROUP BY fa1.ID 
                         GROUP BY fa.id)

which results in an error SQL Error [42601]: ERROR: subquery must return only one column Position: 722

Can someone guide me how to perform this correctly?

just to note the contact table is a slowly changing dimension so it has an end_date column which I also filter in the join but I feel that is of no consquence for this question.

Answer 1

You need to join in another scope that where you are aggregating. For example:

WITH cte_form_answers AS (
    SELECT fa.id,
        MAX(fa.answer) FILTER(WHERE fa.question = 'contact') AS ContactMethod
    FROM formanswers fa
    GROUP BY fa.id
)
SELECT *
FROM cte_form_answers a
LEFT JOIN contacts c ON c.id = fa.id AND c.ContactMethod = a.ContactMethod

Or using another CTE if you prefer:

WITH 
    cte_form_answers AS (
        SELECT fa.id,
            MAX(fa.answer) FILTER(WHERE fa.question = 'contact') AS ContactMethod
        FROM formanswers fa
        GROUP BY fa.id
    ),
    cte_form_contact AS (       
        SELECT *
        FROM cte_form_answers a
        LEFT JOIN contacts c ON c.id = fa.id AND c.ContactMethod = a.ContactMethod
    )
SELECT * FROM cte_form_contact