How to join 2 tables which cardinality is many-to-many

Question

I need to build table, which contain info about customer and cars which he bought by type of buy. So we have

• Customers(customer_id,name,passport...)

• Buys(buy_id,customer_id,car_id,...,buy_type)

• Cars(car_id,car_brand,car_model.....)

How i can build table like this (customer_name,car_brand,car_model) by buy type.

Answer 1

What you're looking for is to use two different JOINs as follows:

SELECT CU.customer_name, CA.car_brand, CA.car_model
FROM Customers CU
JOIN Buys B
  ON CU.customer_id = B.customer_id
JOIN Cars CA
  ON CA.car_id = B.car_id
ORDER BY B.buy_type DESC

Hope this helps! :)