top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Populating fact table with key's from other tables

0 votes
342 views

I hope you guys could help me with solving the following problem.
I am new to SQL (using SQL Server Management Studio 2017).

My goals is to combine the keys of different tables into one table.
Which is shown below. For this example i have 4 source tables of which i only display the keys;

ContractTable:

+------------+---------+
| ContractId | PartyId |
+------------+---------+
| C01        | 1       |
+------------+---------+
| C02        | 2       |
+------------+---------+
| C03        | 3       |
+------------+---------+
| C04        | 4       |
+------------+---------+
| C05        | NULL    |
+------------+---------+
<!-- end snippet -->

PartyTable:

+---------+
| PartyId |
+---------+
| P1      |
+---------+
| P2      |
+---------+
| P3      |
+---------+
| P4      |
+---------+
| P5      |
+---------+
| P6      |
+---------+
| P7      |
+---------+
| P8      |
+---------+

RentalObjectTable:

+----------------+------------+----------+
| RentalObjectId | ContractId | ObjectId |
+----------------+------------+----------+
| R1             | C5         | O1       |
+----------------+------------+----------+
| R2             | NULL       | O2       |
+----------------+------------+----------+
| R3             | C4         | O3       |
+----------------+------------+----------+
| R4             | C1         | O4       |
+----------------+------------+----------+

ObjectTable:

+----------+
| ObjectId |
+----------+
| O1       |
+----------+
| O2       |
+----------+
| O3       |
+----------+
| O4       |
+----------+
| O5       |
+----------+
| O6       |
+----------+

Together they should form the Key/FactTable

+---------+------------+----------------+----------+
| PartyId | ContractId | RentalObjectId | ObjectId |
+---------+------------+----------------+----------+
| P1      | C01        | R4             | O4       |
+---------+------------+----------------+----------+
| P2      | C02        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P3      | C03        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P4      | C04        | R3             | O3       |
+---------+------------+----------------+----------+
| P5      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P6      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P7      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P8      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| NULL    | C5         | R1             | O1       |
+---------+------------+----------------+----------+
| NULL    | NULL       | R2             | O2       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O5       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O6       |
+---------+------------+----------------+----------+

Uptill now i tried the following:

CREATE TABLE FACTS(
ObjectId NVARCHAR(255),
RentalObjectId NVARCHAR(255),
ContractId NVARCHAR(255),
PartyId NVARCHAR(255)
);

INSERT INTO FACTS(ContractId)
SELECT ContractId
FROM ContractTable

UDATE FACTS
SET FACTS.Party_id = PartyTable.PartyId
FROM FACTS FULL OUTER JOIN
PartyTable on PartyTable.PartyId = FACTS.PartyId;

Using that same update statement for the other field.
However this does not work.
I do not get the values on which the query does not find a match.

posted Oct 22, 2018 by Ad Van Dorpen

Looking for an answer?  Promote on:
Facebook Share Button Twitter Share Button LinkedIn Share Button

Similar Questions
0 votes

In context of data warehouse project?

+3 votes

Where does the table variables and temporary tables exist in the memory, in memory or physical drive?

+1 vote

Do i need to write in PL/SQL or is it possible in SQL query?

+1 vote

LookupTable:

userid, mobileid, startedate, enddate , owner
 1 , 1 , 12-12-2000, 01-01-2001, asd 
2 , 2 , 12-12-2000, 01-01-2001, dgs 
3 , 3 , 02-01-2001, 01-01-2002, sdg
 4 , 4 , 12-12-2000, 01-01-2001, sdg

UserInfoTable:

userid, firstname, lastname, address 
1 , tom , do , test 
2 , sam , smith , asds 
3 , john , saw , asdasda
 4 , peter , winston , near by

Mobile:

Mobileid, Name , number, imeinumber 

1 , apple , 123 , 1111111
 2 , nokia , 456 , 2222222
 3 , vodafone , 789 ,` 3333333

CallLogs:

id , Mobileid, callednumbers (string), date , totalduration 
1 , 1 , 123,123,321 , 13-12-2000 , 30 2 , 1 , 123,123,321 , 14-12-2000 , 30 3 , 2 , 123,123,321 , 13-12-2000 , 30 4 , 2 , 123,123,321 , 14-12-2000 , 30 5 , 3 , 123,123,321 , 13-12-2000 , 30 6 , 3 , 123,123,321 , 14-12-2000 , 30 7 , 1 , 123,123,321 , 13-01-2002 , 30 8 , 1 , 123,123,321 , 14-01-2002 , 30

I want a query which will return me the following:
firstname, lastname, mobile.name as mobilename, callednumbers (as concatinated strings from different rows in CallLogs table) and need it for year 2000 example:

firstname, lastname, mobilename, callednumbers 
tom , do , apple , 123,123,321, 123,123,321
 sam , smith , nokia , 123,123,321, 123,123,321
 peter , winston , apple , 123,123,321, 123,123,321

any help will be highly appreciated...

...