Multi-Table GROUP_CONTACT clarification

Question

I have created a rextester for your use: http://rextester.com/MDHD7605

Requirements:

• Should return only 1 (single) row

• The group_concatenated should be the unique/different org data, not the same org data concatenated together 3 times.

What I'm getting is 3 rows returned, and the concatenated data is the same over and over. It should be the data from each 3 rows (of the concatenated data) into 1 column.

The GROUP_CONCAT data should be like:

84864-Twin Cities Spine Center-1984-1985^,99143-Hospital for Special Surgery-1986-1986^^99143-Washington University-1986-1986^

What I'm getting is what you see in the preview/example link.

What am I doing wrong in my query? And how do I correct it?

Answer 1

You need to use GROUP BY instead of correlated subquery:

SELECT genealogy_users.imis_id, genealogy_relations.user_id
       ,genealogy_relations.relation_type
       ,genealogy_users.home_page
       ,GROUP_CONCAT(genealogy_orgs.org_id,'-',genealogy_orgs.org_name,'-',genealogy_relations.start_year,'-',genealogy_relations.end_year,'^') 
          AS org_list
FROM genealogy_users 
JOIN genealogy_relations ON genealogy_users.imis_id = genealogy_relations.user_id 
JOIN genealogy_orgs ON genealogy_relations.org_id = genealogy_orgs.org_id 
WHERE genealogy_users.imis_id = '00003'
GROUP BY genealogy_users.imis_id
        ,genealogy_relations.user_id, genealogy_relations.relation_type
       ,genealogy_users.home_page

Rextester Demo