I have the following tables:

user
car
dealer
user_metrics
  - user_id (FK) (required)
  - dealer_id (FK) (can be null)
  - car_id (FK) (can be null)
  - saved
  - .... other columns

A user can save a car or a dealership, when that happens the user_metrics.saved is set to true and the related car_id or dealership_id is set (car_id and dealership_id are exclusive, only one is set for a row).

I want user A to be able to see all users that have saved the same cars / dealerships.

So, if user A has saved car 1, 2,3 and dealership 5,7, I want to get all users that have saved any of those cars / dealerships.

I thought about inner join on user_metrics, but, I am not sure how to write the entire query that would deliver on this.

What query would allow me to get all users that have saved any of the cars/dealerships a certain user has saved?

CodePudding user response：

If I understand as well maybe the below query solve your problem.

First should find a list of user A has been reserved after that should search which of car or dealer used by another user

with user_saved_data as (
    select um.*,
           u.name,
           ...
    from user_metrics um
             inner join user u
    on um.user_id = u.id
    where um.saved = true
      and u.id = $1 -- User id of user 'A' or any username (Or use other column for create custom condition)
)
select usd.name as current_reserved_user,
       u.name   as reserved_by_user,
       d.*,
       c.*
from user_metrics um
         inner join user u on um.user_id = u.id
         left join user_saved_data usd on usd.dealer_id notnull and usd.dealer_id = um.dealer_id
         left join user_saved_data usd on usd.car_id notnull and usd.car_id = um.car_id
         left join dealer d on um.dealer_id = d.id
         left join car c on um.car_id = c.id