Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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. :)


  2. 0 
    SELECT
    cs.user_id,
    MAX(CASE WHEN tt.taxonomy = 'country' THEN UPPER(country_term.slug) END) AS country,
    MAX(CASE WHEN tt.taxonomy = 'purpose' THEN purpose_term.term_id END) 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 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
GROUP BY
    cs.user_id,
    tt.term_taxonomy_id;
    Login or Signup to reply.
  3. 0

    The relationships here seem to be that a consultant is issued a visa for a country and has purpose(s) for such a visit. Country and purpose(s) can therefore be aggregated eg

    with cte as
(SELECT cs.consultant_id, 
       v.id vid,
       tt.term_taxonomy_id,tt.taxonomy,tt.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 IN('country', 'purpose') 
),
cte1 as
(select consultant_id,vid,taxonomy,cte.term_id,country_term.slug
from cte 
left  JOIN wp_terms country_term ON country_term.term_id = cte.term_id
where cte.taxonomy ='country'
union all
select consultant_id,vid,taxonomy,cte.term_id,purpose_term.slug
from cte 
LEFT JOIN wp_terms purpose_term ON purpose_term.term_id = cte.term_id
where cte.taxonomy ='purpose'
)
select cte1.consultant_id,cte1.vid,
        max(cte1.slug) country,
        group_concat(cte2.slug) purpose
from cte1 
left join cte1 cte2 on cte2.consultant_id = cte1.consultant_id and cte2.vid = cte1.vid and cte2.taxonomy = 'purpose'
where cte1.taxonomy = 'country'
group by cte1.consultant_id,cte1.vid

    note that whilst country is unique to visa a visa could be used many times for different purposes..

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search