Combine a IN with a JOIN in PostgreSQL

Question

There is a table with the ids of mechanics, cars and services. That table is named mcs.One or more mechanics performed a service to the same car.

The logic is that

1. I have an id of either a mechanic or a car and I have to get all its services
2. for each service that I got, I have to get all the mechanics and cars that also had the same service
3. now I have all the ids of mechanics and cars that I want
4. I want to go to their corresponding tables and according to their ids, I want to get their names and the car's plate that is not available in the mechanics table

I am kind of lost. I am half way there I guess. This is what I have for now.

select distinct mcs.mechanic_id, mcs.car_id from mcs where mcs.service_id in 
(select mcs.service_id from mcs where mcs.car_id = 1) 
;

Now, this works and I have all the ids I want, and I have to somehow use them to the following query

select car.name, mechanic.name, car.plate from 
car full join mechanic
on
car.id = mcs.car_id
and 
mechanic.id = mcs.mechanic_id

I dont know how to go on. Something like

select car.name, mechanic.name, car.plate from 
car full join mechanic
on
car.id = mcs.car_id
and 
mechanic.id = mcs.mechanic_id
IN
select distinct mcs.mechanic_id, mcs.car_id from mcs where mcs.service_id in 
(select mcs.service_id from mcs where mcs.car_id = 1) 
;

But this does not work and I dont want to have a lot of loops.

Any suggestions?

Thanks

Answer 1

I think this is what you are needing:

with cteServices as (
select distinct
  service_id
from
  mcs
where
  car_id = 1 -- or mechanic_id = ?
)
select
  mcs.mechanic_id
  ,mechanic.name
  ,mcs.car_id
  ,car.plate
from
  mcs
  inner join
  cteServices on mcs.service_id = cteServices.service_id
  inner join
  car on mcs.car_id = car.car_id
  inner join
  mechanic on mcs.mechanic_id = mechanic.mechanic_id;

http://sqlfiddle.com/#!17/cb7db/5

The first part is a common table expression (CTE) that returns all the services associated with the car_id. You can also change the query to be for a specific mechanic_id.

This CTE is then used to join to mcs in the second part to return all occurrences of those service_ids. You can then join mcs to the car and mechanic tables to get the additional data you are wanting.

Edit: To exclude the selected car in the final query, you would add a where clause like "car.car_id <> 1"

with cteServices as (
select distinct
  service_id
from
  mcs
where
  car_id = 1 -- or mechanic_id = ?
)
select
  mcs.mechanic_id
  ,mechanic.name
  ,mcs.car_id
  ,car.plate
from
  mcs
  inner join
  cteServices on mcs.service_id = cteServices.service_id
  inner join
  car on mcs.car_id = car.car_id
  inner join
  mechanic on mcs.mechanic_id = mechanic.mechanic_id
where
  car.car_id <> 1;

http://sqlfiddle.com/#!17/cb7db/12