Getting data from multiple tables into single row while concatenating some values

Question

I'm trying to retrieve data from tables and combine multiple rows into a single column, without repeating any information.

I have the following tables: profile, qualification, projects.

Profile

pro_id  surname       firstname   
------  -------       ----------
   1    John          James           
   2    King          Fred        
   3    Luxury-Yachts Raymond

Qualification

pro_id  Degree   School               Year
------  ------   ------               -----
   1    MBA      Wharton university   2002        
   1    LLB      Yale University      2001    
   2    BSc      Covington University 1998
   2    BEd      Kellog University    1995

Projects

pro_id  Title                         Year
------  ------                        ------   
   1    Social Networking             2003        
   1    Excavation of aquatic debris  2007    
   2    Design of solar radios        1992
   2    Development of expert systems 2011

I want to retrieve the all of the information for each person, with each person appearing only once in the result. The info on qualifications and projects should each be in their own column (one column for qualifications, another for projects), separated by commas. For example, the results for the above sample data should be:

1  John James             MBA Wharton university 2002, LLB Yale University 2001                     Social Networking 2003, Excavation of aquatic debris 2007, Design of Solar panels 2008
2  King Fred              BSc Covington University 1998, BEd Kellog University 1995, Msc MIT 2011   Design of solar radios 1992, Development of expert systems 2011
3  Raymond Luxury-Yachts

Currently, I have the query:

SELECT pro_id,
       surname,
       firstname,
       group_concat(degree,school,year) AS qual,
       concat(Title,year) AS work
FROM profile,
       LEFT JOIN qualification
           ON qualification.pro_id = profile.pro_id 
       JOIN projects
           ON projects.pro_id = profile.pro_id 
GROUP BY pro_id

For the sample data, this query results in:

1  John James MBA  Wharton university 2002,  Social Networking 2003 
1  John James LLB  Yale University 2001, Excavation of aquatic debris 2007
1  John James MBA  Wharton university 2002,  Social Networking 2003, Excavation of aquatic debris 2007
etc

Note: Raymond Luxury-Yachts isn't present in the current result.

I don't want duplicate result records. Also if the surname does not have any entry in the qualification and projects table, I want the query to return the name and display an empty field in the qualification and projects table instead of omitting them altogether.

Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. Also, the four-space indent is to mark lines as code, not for arbitrary pre-formatted text. For the latter, you can use `
`. — outis, Mar 30 '12 at 07:21

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

Replace LEFT JOIN with JOIN

Select pro_id, surname, firstname, group_concat(degree,school,year) as qual,concat(Title,year) as work 
from profile
join qualification on qualification.pro_id = profile.pro_id 
join projects on projects.pro_id = profile.pro_id group by pro_id

What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited May 23 '17 at 11:56

Community

1
1

answered Mar 30 '12 at 07:01

Māris Kiseļovs

16,957
5
41
48

score 0 · Answer 2 · answered Mar 30 '12 at 07:06

Using Join will fix the issue with displaying values even if there are no records in the projects table. For the first question, you can try making a stored function and calling it from the select statement. This function will take pro_id as parameter, create the concatenated string and return it. That's the only solution for MySQL that I can think of at the moment.

score 0 · Answer 3 · answered Mar 31 '12 at 03:12

I think you are close on your thoughts of group_concat. However, with possible No values (thus leaving nulls), can cause problems. I would have each secondary table pre-concatinated by person's ID and join to THAT result. Eliminates the problem of nulls

SELECT
      p.pro_id,
      p.surname,
      p.firstname,
      PreQConcat.UserQual,
      PrePJConcat.UserWork
   FROM 
      profile p
         LEFT JOIN 
            ( select q.pro_id,
                     group_concat( q.degree, q.school, q.year) AS UserQual
                 from
                     qualification q
                 group by
                     q.pro_id ) PreQConcat
            ON p.Pro_ID = PreQConcat.pro_id

         LEFT JOIN 
            ( select pj.pro_id,
                     concat(pj.Title, pj.year) AS UserWork
                 from
                     projects pj
                 group by
                     pj.pro_id ) PrePJConcat
            ON p.Pro_ID = PrePJConcat.pro_id

You are going through all people anyhow, and want all their respective elements (when they exist) grouped, so why group on a possibility it doesn't exist. Let the JOINED queries run once each, complete with a single result grouped by only those people it had data for, then join back to the original profile person.

Getting data from multiple tables into single row while concatenating some values

3 Answers3

Linked