Join different sizes

Question

I have one table for workers and an other for offices. One worker can be in different offices or in any.

The tables are like:

**Workers**  
ID |  Name
1  |  Ned 
2  |  James
3  |  Tyrion

**WorkersOffices**  
WorkerID |  OfficeID
    1     |    18
    1     |    17 
    2     |    18

I want to join the two tables to get something like this:

**Joined**  
ID |  Name   |  OfficeID
 1  |  Ned    |     18
 1  |  Ned    |     17
 2  |  James  |     18
 3  |  Tyrion |

I have tried the following but it only join one office for worker.

SELECT * FROM workers w
LEFT JOIN workersoffice wo on w.id = wo.workerid

How can I obtain this result?

Would it not be a better idea to link to the table that contains the Office Name — RiggsFolly, Jun 30 '17 at 17:28
I don't see anything wrong with your query. It will definitely display WorkerID 1 twice since he works in 2 offices. — Eric, Jun 30 '17 at 18:26

streetturtle · Accepted Answer · 2017-06-30T17:44:09.523

0

You have to use LEFT JOIN for that:

SELECT * FROM workers w
LEFT JOIN workers_office wo on w.id = wo.worker_id

Which will basically take all the rows from workers table and join office, if it exists for that worker.

Another thing to mention which is not related to the question is to use a singular name of the tables, you can read more about it in this SO answer

edited Jun 30 '17 at 17:44

answered Jun 30 '17 at 17:27

streetturtle

5,472
2
25
43

1

Do or Do Not, there is no "Try" padawan. A **good answers** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. – RiggsFolly Jun 30 '17 at 17:27
I toatlly agree with you. – streetturtle Jun 30 '17 at 17:31
@streetturtle I have tried that but it join one office for worker – URi Jun 30 '17 at 17:32

score 0 · Answer 2 · answered Jun 30 '17 at 17:52

This is what you need:

select w.id,w.name,wo.office_id from workers w left join WorkerOffices wo on w.id = wo.worker_id;

Result set:

+------+--------+-----------+
| id   | name   | office_id |
+------+--------+-----------+
|    1 | Ned    |        18 |
|    1 | Ned    |        17 |
|    2 | James  |        18 |
|    3 | Tyrion |      NULL |
+------+--------+-----------+

score 0 · Answer 3 · edited Oct 08 '17 at 20:53

0

@Uri Your query after join just use "group by workers I'd or name".

It will definitely help you in this. And your join is correct. Then you will b egg able to get no of locations of workers.

edited Oct 08 '17 at 20:53

lebelinoz

4,890
10
33
56

answered Oct 08 '17 at 18:32

Mohini Galhotra

39
1
10

Join different sizes

3 Answers3