CODEWITHSUNDEEP
mysqldatabaserelational-database
Heather H
Heather H

Reputation: 19

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.

Upvotes: 1

Views: 233

Answers (2)

yokoloko
yokoloko

Reputation: 2860

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.

Upvotes: 0

Paul Creasey
Paul Creasey

Reputation: 28824

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

Upvotes: 1

Related Questions