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

General
1

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

2

The issue with your query is that you’re trying to join wp_terms for both country and purpose in a way that results in separate rows for each match. Instead, you need to combine the country and purpose terms for the same user_id in a way that aligns the country and purpose values into the same row.

To achieve this, you can split the country and purpose into separate subqueries (or Common Table Expressions, CTEs) and then join them back together. Here’s how you can structure your query:

sql

Copy code

WITH country_terms AS (
    SELECT
        cs.user_id,
        UPPER(country_term.slug) AS country
    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 = 'country'
    LEFT JOIN wp_terms country_term ON
        tt.term_id = country_term.term_id
    WHERE
        cs.user_id = 2
),
purpose_terms AS (
    SELECT
        cs.user_id,
        purpose_term.term_id AS purpose_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 = 'purpose'
    LEFT JOIN wp_terms purpose_term ON
        tt.term_id = purpose_term.term_id
    WHERE
        cs.user_id = 2
)
SELECT
    COALESCE(ct.user_id, pt.user_id) AS user_id,
    ct.country,
    pt.purpose_id
FROM
    country_terms ct
FULL OUTER JOIN purpose_terms pt ON ct.user_id = pt.user_id;

Explanation:

  1. country_terms CTE: Extracts user_id and the country values for the specified user_id.
  2. purpose_terms CTE: Extracts user_id and the purpose_id values for the same user_id.
  3. FULL OUTER JOIN: Combines country_terms and purpose_terms so that each user_id has a row with both country and purpose_id. This handles cases where one of them might be NULL.

Key Points:

  • The COALESCE(ct.user_id, pt.user_id) ensures that even if one side is NULL, the user_id is included.
  • The FULL OUTER JOIN ensures that rows from both country_terms and purpose_terms are retained, even if one is NULL.

Result:

This should produce the desired output:

plaintext

Copy code

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

If your database does not support FULL OUTER JOIN (e.g., MySQL), you can simulate it with a UNION query:

sql

Copy code

SELECT
    ct.user_id,
    ct.country,
    pt.purpose_id
FROM
    country_terms ct
LEFT JOIN purpose_terms pt ON ct.user_id = pt.user_id
UNION
SELECT
    pt.user_id,
    ct.country,
    pt.purpose_id
FROM
    purpose_terms pt
LEFT JOIN country_terms ct ON pt.user_id = ct.user_id;

This approach ensures compatibility with databases like MySQL.