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
Advertisement
Answer
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)