CODEWITHSUNDEEP
mysql
Dhaval
Dhaval

Reputation: 901

Get multiple value in single row using join

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?

Upvotes: 1

Views: 137

Answers (2)

Vladimir Kirilov
Vladimir Kirilov

Reputation: 59

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

Upvotes: 0

DMorillo
DMorillo

Reputation: 86

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

Upvotes: 2

Related Questions