CODEWITHSUNDEEP
mysql
darkboy
darkboy

Reputation: 35

people who rented the same car?

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     |
+----+---------+---------+

Upvotes: 1

Views: 99

Answers (2)

Turtlean
Turtlean

Reputation: 579

I'd also use a WITH Common Table Expression but I'd rather remove the users that rented more than one model and those who only rented one car:

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).

with user_only_same_model as
(
  select user.id, count(*)
  from car 
  join rent on car.rent = rent.rentId
  join user on rent.userId = user.id
  group by (user.id) having count(distinct car.model) = 1 and count(*) > 1
)
select distinct user.id, user.name, car.model
from user_only_same_model as usm
join user on user.id = usm.id 
join rent on rent.userId = user.id
join car on car.rent = rent.rentId;

This returns only Alex:

+----+------+-------+
| id | name | model |
+----+------+-------+
|  1 | alex | kia   |
+----+------+-------+

But I think it's the right answer because Michael rented both a kia and a hyundai whereas Samuel just rented one car.

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17655

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;

Upvotes: 1

Related Questions