Get multiple value in single row using join

Question

I want to get multiple values in a single row which matches with primary table. Below are the example tables:

members:
- id
- name
- status

address:
- id
- ref_id(member id)
- address1
- state

contacts:
- id
- ref_id(member id)
- phone
- email

mem_cc
- id
- ref_id(member id)
- category_id
- coverage_id

I'm using below query to create view to get all the records in single view so I can query that view to display a list page:

SELECT a.id, a.name, a.status, b.address1, b.state, c.phone, d.category_id, d.coverage_id 
FROM members a LEFT JOIN address b 
ON a.id = b.ref_id
LEFT JOIN contacts c 
ON a.id = c.ref_id 
LEFT JOIN mem_cc d 
ON a.id = d.ref_id

Now case like Member A is subscribed with 3 coverages or 3 categories then it'll show me Member A's record three times, I want to get Member A record in table single time with covering all categories and coverages in that single row. Question is how to do that?

DMorillo · Accepted Answer · 2014-06-30 08:19:37Z

2

I believe you need function "group_concat" when selecting the category:

select a.id,a.name,a.status,b.address1,b.state,c.phone,
group_concat(d.category_id, d.coverage_id) 
from members a left join address b on a.id = b.ref_id 
left join contacts c on a.id = c.ref_id and left join mem_cc d on a.id = d.ref_id
group by a.id

answered Jun 30, 2014 at 8:19

DMorillo

864 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dhaval Over a year ago

Thanks for your quick reply. I've already tried that but it returns only one record

Dhaval Over a year ago

Got it, I forgot to mentioned "group by a.id" at the end of the query. Thanks a lot for you reply.

Vladimir Kirilov · Accepted Answer · 2014-06-30 08:46:10Z

As DMorillo already said you will have to use grouping. In this way you will get one record for the user and in the different columns you can then group the results as necessary. If you were thinking of extra columns popping up based on your joins then I don't think this is possible. See if the query below works for your case.

SELECT a.id,
       a.name,
       a.status, 
       -- This group_concat will produce something like "5th street - Alabama" 
       -- separated with newlines
       -- Check for NULL values since you are using left joins
       GROUP_CONCAT(IFNULL(CONCAT(b.address1, ' - ', b.state), ''))
                    DELIMITER '\n') AS address,
       -- Same goes for phone numbers. Default delimiter is comma.
       GROUP_CONCAT(IFNULL(c.phone, '') DELIMITER ','),
       -- Now you can group your categories.
       GROUP_CONCAT(IFNULL(CONCAT(d.category_id,' ', JOINEDCATEGORYNAME), '') AS category,
       GROUP_CONCAT(IFNULL(CONCAT(d.coverage_id,' ', JOINEDCOVERAGENAME), '') AS coverage
FROM members a 
LEFT JOIN address b ON a.id = b.ref_id
LEFT JOIN contacts c ON a.id = c.ref_id 
LEFT JOIN mem_cc d ON a.id = d.ref_id
-- Here probably your inner joins to categories table and coverage table
GROUP BY a.id

Collectives™ on Stack Overflow

Get multiple value in single row using join

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related