I have a project about github but I represent like rent a car db for better understanding. I must find people who rented the same car from db. I have a 3 table: user, rent and car.
User Table
+----+---------+
| id | name |
+----+---------+
| 1 | alex |
| 2 | samuel |
| 3 | michael |
+----+---------+
Rent Table
+--------+--------+
| rentId | userId |
+--------+--------+
| 68 | 1 |
| 77 | 3 |
| 93 | 2 |
| 99 | 3 |
| 105 | 1 |
+--------+--------+
Car Table
+---------+------+---------+
| model | km | rent |
+---------+------+---------+
| kia | 123 | 68 |
| bmw | 389 | 93 |
| hyundai | 7979 | 99 |
| kia | 3434 | 77 |
| kia | 6565 | 105 |
+---------+------+---------+
I tried this query. (I used rentId for better understanding the relations)
SELECT id, name, rentId, model FROM user JOIN rent ON id = user_ID JOIN car ON rentID = rent ORDER BY model
It returns
+----+---------+---------+---------+
| id | name | rent_ID | model |
+----+---------+---------+---------+
| 1 | alex | 68 | kia |
| 2 | samuel | 93 | bmw |
| 3 | michael | 77 | kia |
| 3 | michael | 99 | hyundai |
| 1 | alex | 105 | kia |
+----+---------+---------+---------+
But I want to like this. It only show people who rented the same car. And If the same person hired another time, don't show it either(no duplicate).
+----+---------+---------+
| id | name | model |
+----+---------+---------+
| 1 | alex | kia |
| 3 | michael | kia |
+----+---------+---------+
Using a CTE work out which models have more than 1 hire to more than 1 person then join the other tables to get the detail using distinct to dedupe
with cte as
(select c.model,count(distinct userid)
from car c
join rent r on r.rentid = c.rent
group by c.model having count(distinct userid) > 1
)
select distinct c.model,r.userid,u.name
from cte
join car c on c.model = cte.model
join rent r on r.rentid = c.rent
join usr u on u.id = r.userid;