CODEWITHSUNDEEP
pythonmysqlsql
darkboy
darkboy

Reputation: 35

Which cars have been rented together how many times? Which one is more easy to do MySQL or python?

I have a project about github but I represent like rent a car db for better understanding. I must find which cars have been rented together how many times from db. I have a 2 table: rent and car.

Rent Table
+----+---------+
| id | date    |
+----+---------+
| 68 | 2010    |
| 2  | 2011    |
| 7  | 2012    |
+----+---------+
Car Table
+---------+---------+-------+
| model   | rent_ID | km    |
+---------+---------+-------+
| kia     | 68      | 4343  |
| bmw     | 68      | 7679  |
| kia     | 2       |  8464 |
| hyundai | 2       | 1234  |
| bmw     | 2       | 9004  |
| kia     | 7       | 11001 |
| hyundai | 7       | 7654  |
+---------+---------+-------+

I want to like this type of return

+-------+-------------+------+
| model | model       |count |
+-------+-------------+------+
| kia   | bmw         | 2    |
| kia   | hyundai     | 2    |
| bmw   | hyundai     | 1    |
+-------+-------------+------+

I tried some queries to find this but I can't. Also, I think I can do this with multi dimensional array in python. But If I have a a lot of cars, It can takes a lot of time. Which way is better and optimal?

Upvotes: 2

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

This is simple to do in MySQL. To get the counts for any pair of models, use a self-join and aggregation:

select c1.model, c2.model, count(*)
from car c1 join
     car c2
     on c1.rent_id = c2.rent_id and
        c1.model < c2.model
group by c1.model, c2.model;

Upvotes: 1

Related Questions