Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

378
Views
SQL select for many to many relationship using binding table

I am struggling with writing a select for diagram on the picture.picture

What I want to do is write a select, which will show me details of a car repair. As you can see in table Repairs there are only 2 attributes but I am not sure if it's necessary to add more, especialy those from employees_list and parts_list, since I want to show repair data for every vehicle by it's plate_number. By repair data I mean repair id, vehicle plate_number, all employees working on the repair and all parts used on the repair. If my diagram is wrong, please help me fix it and I have no idea how to write select for this because of the many to many relation and the use of binding tables.

over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

This is not so hard as it may seem.

First, obviously, we have to select cars:

select vehicles.* from vehicles

then, let's join repairs:

select 
    vehicles.* 
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle.id

We don't need data from repairs in resule set, so we just join it but not mention in 'select' part.

Then we have to join parts needed for repair, and info about parts itself:

select 
    vehicles.* 
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle.id
    inner join parts_list on parts_list.repair_id = repairs.id
    inner join parts on parts_list.part_id = parts.id

For that query we get amout of rows equivalent of amount of parts needed for repair. But it would be more easy to handle such data in code if we aggregate all of them into json column. So in result set we willsee something like: vehicle_id, vehicle_part, parts_needed_as_json

Lets aggregate this:

select 
    vehicles.*, json_agg(parts.*) as parts_needed 
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle_id
    inner join parts_list on parts_list.repair_id = repairs.id
    inner join parts on parts_list.part_id = parts.id
group by vehicles.id, repairs.id

Now you can add same logic for employees:

select 
    vehicles.*, 
    json_agg(parts.*) as parts_needed,
    json_agg(employes.*) as employees_needed
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle.id
    inner join parts_list on parts_list.repair_id = repairs.id
    inner join parts on parts_list.part_id = parts.id
    inner join employees_list on employes_list.repair_id = repairs.id
    inner join employees on employees_list.employee_id = employees.id
group by vehicles.id, repairs.id

BTW, I suggest you to rename your tables to lowercase and singulars. Like: 'repair', 'employee' and 'vehicle';

Also, name your binding tables like: 'repair_part' and 'repair_employee'. Some people even suggest to arrange related tables in that names by alphabet, like: 'employee_repair' and 'part_repair', but I think it's not required;

Maybe this is a question of taste but in most cases this leads to more readable queries.

I.e, the query above will looks like:

select 
    vehicle.*, 
    json_agg(part.*) as parts_needed,
    json_agg(employee.*) as employees_needed
from vehicle
    inner join repair on vehicle.id = repair.vehicle_id
    inner join parts_repair on parts_repair.repair_id = repair.id
    inner join part on parts_repair.part_id = part.id
    inner join employees_repair on employees_repair.repair_id = repair.id
    inner join employee on employees_repair.employee_id = employee.id
group by vehicle.id, repair.id

Note how elegant 'on' conditions looks now: parts_repair.part_id = part.id, parts_repair.part_id = part.id

Sorry for bad english

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!