-1

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

Andy
  • 127
  • 2
  • 9

1 Answers1

0

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)

Kaede
  • 198
  • 1
  • 11
  • thank you, that makes sense, I'm very inexperienced in multiple joins, now that I think of it, after first join, one of the dimension tables and the fact table should be combined then I can join others by the related keys. I assume instead of select * I can select product.price and etc. (only the fields that I want) – Andy Oct 14 '20 at 15:36
  • Instead of product.price you could do P.price since you defined it with the left joints – Kaede Oct 14 '20 at 22:19