Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

380
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda