what is the best way to join records of multiple dimension tables that are all connected by a common fact table

Question

So I have a fact table that connects multiple dimension tables. Say for dimension tables I have customer, product, and date.

Customer has id, name

Product has id, price

Date has id, year

Fact table has cus_id, pro_id, and date_id. All ids are foreign keys from the above tables.

if I want to display a table that has cus_name, pro_price, date_year.

What would be the query that efficiently join these tables.

Thank you.

To answer the question from the comment what I have attempted, I have not attempted anything yet simply because I don't know how. I understand how to join tables of customer and product, if they are related by a foreign key, but in this case, customer is only related to the fact table, I can join customer and fact table by join on customer.id = fact_table.cus_id, but I do not know how to join customer with product.

Thank you

Answer 1

SELECT * FROM Fact F

LEFT JOIN Customer C ON C.id = F.cus_id

LEFT JOIN Product P ON P.id = F.pro_id

LEFT JOIN Date D ON D.id = F.date_id

Since youre joining on id's they cant be double This will display every row in Fact table (with ForEach loop )

note: This does not display all data because not all date might be in Fact (but since i assume Fact == invoice so that wouldnt be needed)