How do I merge two LEFT JOIN rows that cannot coexist into one?

Question

So I have a result like this gotten from multiple LEFT JOIN statement where one join is available, the other is null

user_id | country | purpose_id
2         CA       NULL
2         NULL     367
2         NG       NULL
2         NULL     368

I want to be able to convert this to

user_id | country | purpose_id
2         CA       367
2         NG       368

But I cannot seem to get it with my JOIN statement

SELECT
    cs.user_id,
    UPPER(country_term.slug),
    purpose_term.term_id
FROM
    wp_user_services cs
INNER JOIN wp_posts v ON
    v.ID = cs.visa_id AND v.post_status = 'publish'
INNER JOIN wp_term_relationships tr ON
    tr.object_id = v.ID
INNER JOIN wp_term_taxonomy tt ON
    tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy IN('country', 'purpose')
LEFT JOIN wp_terms country_term ON
    tt.term_id = country_term.term_id AND tt.taxonomy = 'country'
LEFT JOIN wp_terms purpose_term ON
    purpose_term.term_id = tt.term_id AND tt.taxonomy = 'purpose'
WHERE
    cs.user_id = 2;

Anyone with ideas?

EDIT: Fiddle

Answer 1

I solved my problem with nested query after trying to write a PHP script, I realised I used nested loop. It works now.

SELECT DISTINCT
    cs.consultant_id,
    UPPER(country_term.slug) country,
    purpose.term_id
FROM
    wp_consultant_services cs
INNER JOIN wp_posts v ON
    v.ID = cs.visa_id AND v.post_status = 'publish'
INNER JOIN wp_term_relationships tr ON
    tr.object_id = v.ID
INNER JOIN wp_term_taxonomy tt ON
    tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'country'
INNER JOIN wp_terms country_term ON
    tt.term_id = country_term.term_id
INNER JOIN(
    SELECT
        purpose_term.term_id term_id,
        tr2.object_id object_id
    FROM
        wp_term_relationships tr2
    INNER JOIN wp_term_taxonomy tt2 ON
        tt2.term_taxonomy_id = tr2.term_taxonomy_id AND tt2.taxonomy = 'purpose'
    INNER JOIN wp_terms purpose_term ON
        purpose_term.term_id = tt2.term_id
) purpose
ON
    purpose.object_id = tr.object_id
WHERE
    cs.consultant_id = 2 AND cs.visa_id IS NOT NULL AND cs.status = 1;

I would love ideas on how to improve this if there is room for improvement. :)