ukerandi
asked on
Relation ship build query
I have 2 tables
one is Fact Table and Customer
Fact Table fields are
Originator nvarchar(60),
[Customer Number] nvarchar(7) ,
Cust_Seq int FOREIGN KEY REFERENCES Customers(cust_num),
[Item code] nvarchar(10) FOREIGN KEY REFERENCES Item([Item code]),
[DateKey] INT FOREIGN KEY REFERENCES DimDate(DateKey)
and
Customer Table
cust_num nvarchar(7),
cust_seq INT PRIMARY KEY,
Company_name nvarchar(60),
Address1 nvarchar(60),
Address2 nvarchar(60),
Address3 nvarchar(60),
Address4 nvarchar(60),
City nvarchar(60),
Zip_Code nvarchar(60),
County_State nvarchar(60),
Country nvarchar(60),
Telephone nvarchar(60),
Email nvarchar(60)
My question is
Customer Number is can't take unique,but custpmer sequence number can take unique.
becuase one customer has many orders and but customer sequence number will generate unique number according to orders(0,1,2,3...etc)
if i comapre with Fact table cust_seq field and customer table cust_seq is can't equal,becuase i will get wrong results
for example customer1 and customer 2 has same sequence number.
So can some one show me how to combine Customer table and Fact Table using which Fields(This for Dataware House).
and i need custo_seq becuase some time some time same customer address is different according cust_seq number.
one is Fact Table and Customer
Fact Table fields are
Originator nvarchar(60),
[Customer Number] nvarchar(7) ,
Cust_Seq int FOREIGN KEY REFERENCES Customers(cust_num),
[Item code] nvarchar(10) FOREIGN KEY REFERENCES Item([Item code]),
[DateKey] INT FOREIGN KEY REFERENCES DimDate(DateKey)
and
Customer Table
cust_num nvarchar(7),
cust_seq INT PRIMARY KEY,
Company_name nvarchar(60),
Address1 nvarchar(60),
Address2 nvarchar(60),
Address3 nvarchar(60),
Address4 nvarchar(60),
City nvarchar(60),
Zip_Code nvarchar(60),
County_State nvarchar(60),
Country nvarchar(60),
Telephone nvarchar(60),
Email nvarchar(60)
My question is
Customer Number is can't take unique,but custpmer sequence number can take unique.
becuase one customer has many orders and but customer sequence number will generate unique number according to orders(0,1,2,3...etc)
if i comapre with Fact table cust_seq field and customer table cust_seq is can't equal,becuase i will get wrong results
for example customer1 and customer 2 has same sequence number.
So can some one show me how to combine Customer table and Fact Table using which Fields(This for Dataware House).
and i need custo_seq becuase some time some time same customer address is different according cust_seq number.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cust_seq also can't be unique
Since the combination of the customer_no and cust_seq is uinique you can join the tableson those two columns, i.e.
select colums_you_need
from Fact_table f left join customer_table c on f.[Customer Number] =c.cust_num and f.Cust_Seq =c.cust_seq
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Example :
Customer No cust_seq
E0001 0
E0001 1
E0001 2
E0002 0
E0002 1
E0002 2