How do you merge rows from 2 SQL tables without duplicating rows?

Question

Hi,

I guess this query is a little basic and I should know more about SQL but haven't done much with joins yet which I guess is the solution here.

What I have is a table of people and a table of job roles they hold. A person can have multiple jobs and I wish to have one set of results with a row per person containing their details and their job roles.

Two example tables (people and job_roles) are below so you can understand the question easier.

People

 id |  name  |    email_address   |  phone_number
 1  |  paul  |  [email protected]  |  123456
 2  |   bob  |  [email protected]   |  567891
 3  |  bart  |  [email protected]  |  987561

job_roles

 id  |  person_id  |     job_title   | department
  1  |      1      |     secretary   |    hr
  2  |      1      |     assistant   |   media
  3  |      2      |      manager    |    IT
  4  |      3      |  finance clerk  |  finance
  4  |      3      |      manager    |    IT

so that I can output each person and their roles like such

Name: paul
Email Address: [email protected]
Phone: 123456
Job Roles: 
Secretary for HR department
Assistant for media department
_______
Name: bob
Email address: [email protected]
Phone: 567891
Job roles:
Manager for IT department

So how would I get each persons information (from the people table) along with their job details (from the job_roles table) to output like the example above. I guess it would be some kind of way of merging their jobs and their relevant departments into a jobs column that can be split up for output, but maybe there is a better way and what would the sql look like?

Thanks

Paul

PS it would be a mySQL database if that makes any difference

Answer 1

It looks like a straight-forward join:

SELECT p.*, j.*
  FROM People AS p INNER JOIN Roles AS r ON p.id = r.person_id
 ORDER BY p.name;

The remainder of the work is formatting; that's best done by a report package.

---

Thanks for the quick response, that seems a good start but you get multiple rows per person like (you have to imagine this is a table as you don't seem to be able to format in comments):

id | Name | email_address    | phone_number | job_role  | department
 1 | paul | [email protected] | 123456       | secretary | HR
 1 | paul | [email protected] | 123456       | assistant | media
 2 | bob  | [email protected]  | 567891       | manager   | IT

I would like one row per person ideally with all their job roles in it if that's possible?

It depends on your DBMS, but most available ones do not support RVAs - relation-valued attributes. What you'd like is to have the job role and department part of the result like a table associated with the user:

+----+------+------------------+--------------+------------------------+
| id | Name | email_address    | phone_number |   dept_role            |
+----+------+------------------+--------------+------------------------+
|    |      |                  |              | +--------------------+ |
|    |      |                  |              | | job_role   | dept  | |
|  1 | paul | [email protected] | 123456       | | secretary  | HR    | |
|    |      |                  |              | | assistant  | media | |
|    |      |                  |              | +--------------------+ |
+----+------+------------------+--------------+------------------------+
|    |      |                  |              | +--------------------+ |
|    |      |                  |              | | job_role   | dept  | |
|  2 | bob  | [email protected]  | 567891       | | manager    | IT    | |
|    |      |                  |              | +--------------------+ |
+----+------+------------------+--------------+------------------------+

This accurately represents the information you want, but is not usually an option.

So, what happens next depends on your report generation tool. Using the one I'm most familiar with, (Informix ACE, part of Informix SQL, available from IBM for use with the Informix DBMSs), you would simply ensure that the data is sorted and then print the name, email address and phone number in the 'BEFORE GROUP OF id' section of the report, and in the 'ON EVERY ROW' section you would process (print) just the role and department information.

It is often a good idea to separate the report formatting from the data retrieval operations; this is an example of where it is necessary unless your DBMS has unusual features to help with the formatting of selected data.

---

Oh dear that sounds very complicated and not something I could run easily on a mySQL database in a PHP page?

The RVA stuff - you're right, that is not for MySQL and PHP.

On the other hand, there are millions of reports (meaning results from queries that are formatted for presentation to a user) that do roughly this. The technical term for them is 'Control-Break Report', but the basic idea is not hard.

You keep a record of the 'id' number you last processed - you can initialize that to -1 or 0. When the current record has a different id number from the previous number, then you have a new user and you need to start a new set of output lines for the new user and print the name, email address and phone number (and change the last processed id number). When the current record has the same id number, then all you do is process the job role and department information (not the name, email address and phone number). The 'break' occurs when the id number changes. With a single level of control-break, it is not hard; if you have 4 or 5 levels, you have to do more work, and that's why there are reporting packages to handle it.

So, it is not hard - it just requires a little care.

Answer 2

Doing it the way you're wanting would mean the result set arrays could have infinite columns, which would be very messy. for example, you could left join the jobs table 10 times and get job1, job2, .. job10.

I would do a single join, then use PHP to check if the name ID is the same from 1 row to the next.

Answer 3

RE:

I was hoping SQL could do something clever and join the rows together nicely so I had essentially a jobs column with that persons jobs in it.

You can get fairly close with

SELECT  p.id, p.name, p.email_address, p.phone_number,
group_concat(concat(job_title, ' for ', department, ' department')  SEPARATOR '\n') AS JobRoles
FROM People AS p 
    INNER JOIN job_roles AS r ON p.id = r.person_id
GROUP BY p.id, p.name, p.email_address, p.phone_number
 ORDER BY p.name;

Answer 4

One way might be to left outer join the tables and then load them up into an array using

$people_array =array(); 
while($row1=mysql_fetch_assoc($extract1)){ 
$people_array[] = $row1;  
} 

and then loop through using

 for ($x=0;$x<=sizeof($people_array;) 
    {  
echo $people_array[$x][id]; 
echo $people_array[$x][name]; 

for($y=0;$y<=$number_of_roles;$y++) 
{ 
 echo $people_array[$x][email_address]; 
 echo $people_array[$x][phone_number]; 
    $x++; 
} 
     } 

You might have to play with the query a bit and the loops but it should do generally what you want.For it to work as above every person would have to have the same number of roles, but you may be able to fill in the blanks in your table