How to combine data from multiple tables using SQL?

Question

I have following tables in my MySql database :

+----------------------------------------------+
                      PROJECT
+----------------------------------------------|
+----------------------------------------------|
  project_id | team_size | from_date
+----------------------------------------------|
    1        |   34      |  1 Dec 2010
+----------------------------------------------|
    2        | 2         | 2 Jan 1902
+----------------------------------------------|
    3        | 99        | 15 Aug 1947
+----------------------------------------------+

+----------------------------------------------+
        Technologies
+----------------------------------------------+
+----------------------------------------------+
 technology_id | technology_name
+----------------------------------------------+
       1       | Java
+----------------------------------------------+
       2       | CPP
+----------------------------------------------+
       3       | Hibernate
+----------------------------------------------+
       4       | EJB
+----------------------------------------------+
       5       | Python
+----------------------------------------------+
       6       | Hadoop
+----------------------------------------------+
       7424    | Perl
+----------------------------------------------+

To link Project and Technologies tables I have following table :

+----------------------------------------------+
        Project_Technologies
+----------------------------------------------+
+----------------------------------------------+
   Project_ID   | Technology_ID
+----------------------------------------------+
    1           | 2
+----------------------------------------------+
    1           | 7424
+----------------------------------------------+
    2           | 1
+----------------------------------------------+
    2           | 3
+----------------------------------------------+
    2           | 4
+----------------------------------------------+
    2           | 5
+----------------------------------------------+

I want to show data in one table in the form of rows and columns in UI. For example :

+---------------------------------------------------+
  project_id | team_size | from_date | technologies 
|---------------------------------------------------|
|     1      |   34      | 1 Dec 2010| CPP, Perl    |
|---------------------------------------------------|
|     2      |   2       | 2 Jan 1902| Java, Hibernate, EJB, Python |
|---------------------------------------------------|
|     3      |   99      |15 Aug 1947|              |
+---------------------------------------------------+

I am not able to form sql query to get something like this. I have tried following query which is giving me duplicate rows.

select pr.project_id,pr.team_size,pr.from_date,tech.technology_name
from project pr, project_technologies ptech, technologies tl
where pr.project_id=ptech.project_id and ptech.technology_id=tl.technology_id

I would like to know how to avoid duplicate rows? Currently this query is giving me 2 rows when project_id=1 and 4 rows when project_id=2

@CodeBuzz, ya just now I tried distinct but I am getting same output. I'll add output of current query in question. — Shekhar
– Shekhar, Commented Nov 9, 2011 at 7:19

Stefan H · Accepted Answer · 2011-11-09 07:57:30Z

2

This should do the trick... a group by and a group_concat

select 
    pr.project_id, 
    pr.team_size, 
    pr.from_date, 
    GROUP_CONCAT(tech.technology_name separator ', ') as technologies
from  
    project pr 
    JOIN project_technologies ptech ON pr.project_id=ptech.project_id
    JOIN technologies tl ON ptech.technology_id=tl.technology_id
GROUP BY
    pr.project_id, 
    pr.team_size, 
    pr.from_date

EDIT fixed to include JOIN clause

edited Nov 9, 2011 at 7:57

answered Nov 9, 2011 at 7:17

Stefan H

6,6934 gold badges26 silver badges35 bronze badges

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

6 Comments

vicky Over a year ago

I think you should use inner join operation in mysql to join two tables on common attributes, that has feature to avoid duplicated too.

Stefan H Over a year ago

I added the join causes, I had originally just introduced the fix that the OP NNEEDS, but I see the wisdom in fixing that as well. Thanks!

Jim H. Over a year ago

If you make that an outer join you'll get the 3rd row with a null column from the group_concat. MySQL's default join is inner.

Stefan H Over a year ago

That's a good point, to be closer to the UI output that the OP showed, they SHOULD be left outer joins.

Shekhar Over a year ago

Hi Stefan, your modified answer which includes JOIN clause is not working. I am getting following error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN technologies tl ON ptech.technology_id=tl.technology_id" GROUP BY

|

Devart · Accepted Answer · 2011-11-09 07:20:10Z

1

Try this one -

SELECT p.*, GROUP_CONCAT(t.technology_name) technologies FROM project p
  JOIN project_technologies pt
    ON pt.project_id = p.project_id
  JOIN technologies t
    ON t.technology_id = pt.technology_id
GROUP BY p.project_id;

answered Nov 9, 2011 at 7:20

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

Stefan H Over a year ago

you will not be able to select items not included in the group by unless they are aggregate functions...

Stefan H Over a year ago

Sorry, I should have rather said you SHOULD not. To my knowledge it is against the ANSI sql standard and in some modes of mysql, you actually cannot return non aggregate columns that are not specified in a group by.

Devart Over a year ago

@Stefan H You are right. ...and I hope the project.project_id is unique;-)

Hamidreza · Accepted Answer · 2011-11-09 07:21:19Z

1

USE THIS:

SELECT PROJECT.*, Technologies.* FROM Project_Technologies
INNER JOIN PROJECT ON Project_Technologies.Project_ID = PROJECT.project_id
INNER JOIN Technologies ON Project_Technologies.Technology_ID = Technologies.technology_id

answered Nov 9, 2011 at 7:21

Hamidreza

1,9254 gold badges29 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to combine data from multiple tables using SQL?

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related