MySQL relational database query, correct terminology?

I think my issue with databases stems from not knowing the correct terminology to help me find an answer myself so I'll explain a generic version of what I'm doing and hopefully you can point some tutorials my way or give me some terms to check into.

Let's use an example of an employee directory.

Each employee can have multiple locations, multiple job duties which pull from a separate table. Example tables & some data, let's just focus on the multiple locations.

employees

Main employee data

- id (ex: 400)

- first (ex: John)

- last (ex: Doe)

locations

Unique list of locations

- id (ex: 3000)

- title (ex: FakeCo, LLC)

map_employees_locations

Tie ANY number of locations to an employee

- id

- employee_id (ex: 400)

- location_id (ex: 3000)

I'm struggling with the logic of how a single query would return something like this:

John

Doe

FakeCo, LLC

AnotherCo, LLC

It seems I would have to run a query to get the employee data, then 开发者_运维问答within a nested query, grab locations associated with the employee id, etc... If there was only one location per employee, it would be a simple join, I just don't know how to handle the multiples.

Let me know if I'm way off, I'm just struggling.

You would join all of the tables together like this

select e.id,e.first,e.last,l.id,l.title
from employees e
inner join map_employees_locations el
on el.employee_id = e.id
inner join locations l
on el.location_id = l.id
where e.first = 'John'
AND e.last = 'Doe'

This would return data like this:

e.id    e.first   e.last   l.id    l.title
------------------------------------------------
1       John      Doe      1       FakeCo, LLC
1       John      Doe      2       AnotherCo, LLC

If you want only one line per employee you should maybe use group concat

select id, e.last, e.first
     group_concat(l.title separator ',' ) as locations 
from employee e
join location l on l.employee_id = e.id
group by e.id

Not sure about the syntax cos i'm more aware of postgres but this should do the job.