How to join through an N:N relationship table in SQL

Question

I need some help to do a join through an N:N relationship table.

In my first table I have car rental transactions:

Transaction
-----------------------
id : carID : date
1  : 2     : 01-01-2017
2  : 2     : 01-01-2017
3  : 3     : 01-01-2017
4  : 4     : 01-01-2017

My car table then has:

Car
---------------
id : carInfo
2  : brown car
3  : red car
4  : green car
5  : orange car

I also have a store table:

Store
---------------
id : storeInfo
3  : city3
4  : city4
5  : city5

My relationship table is a combination of car and store because the car can move from store to store. In other words it's all the stores in which the car has been found at one time or another:

Relationship Table
------------------
carID : storeID
2     : 3
2     : 4
3     : 3

The query I want to do is get a list of record transactions to include the car and store info assuming I only want the transactions that were from a store. So for example I want all transactions from store 3. I specifically want this resulting table (SELECT transaction.*, car.carInfo, store.storeInfo WHERE store.ID=3) with the appropriate joins of course.

Joined Transaction result table
---------------------------------------
id : date : carID : carInfo : storeID : storeInfo 

where storeID=3

I know I can do a LEFT JOIN in the transaction table and the car table but how do I also join the store table through the relationship table. In other words I currently have (which I believe is wrong):

SELECT transaction.*, car.carInfo LEFT JOIN car ON transaction.carID=car.ID

The question is how do I do the next JOIN so that I can just add at the end WHERE store.ID=3 as well as get the store.storeInfo column. I'm struggling with the missing join...

Answer 1

SELECT 
    transaction.*,
    car.carInfo,
    store.storeInfo
FROM 
    transaction
INNER JOIN
    relationshipTable ON transaction.carID=relationshipTable.carID
LEFT JOIN
    store ON relationshipTable.storeID=store.ID
WHERE   
    store.id=3