Denormalised -> Normalised
-
Hi All
Hope someone can shed some light on a little problem I'm having.
Environment is SQL Server 2000 SP3 on Win2k SP2.
I have the following tables, ( shown with sample data ) in a product hierarchy.
Table - Product
IDPrd IDPrdG1CR IDPrdG1DR
----- --------- ---------
L1 11936 11938
L2 11936 11938
L3 11937 11939
L4 11936 11938
L5 11937 11939
L6 11937 11939
Table - ProductG1
IDPrdG1
-------
11936
11937
11938
11939
Table - ProductG2
IDPrdG2IDPrdG1
------- -------
20513 11936
20514 11937
20515 11938
20516 11939
Table - ProductG3
IDPrdG3IDPrdG2 RptPrd
------- ------- ------
30452 20513 1
30453 20514 1
30454 20515 1
30455 20516 0
What I need to show is a denormalised record showing all the ID's related to a single product. Note that a product is related to one set of ID's for credits and another for debits. Also a product can be reported or not. This is indicated by the RptPrd field in the ProductG3 table.
What I've come with so far is,
SELECT Prd.IDPrd, G1CR.IDPrdG1 as IDPrdG1CR, G2CR.IDPrdG2 as IDPrdG2CR
, G3CR.IDPrdG3 as IDPrdG3CR, G1DR.IDPrdG1 as IDPrdG1DR
, G2DR.IDPrdG2 as IDPrdG2DR, G3DR.IDPrdG3 as IDPrdG3DR
, G3CR.RptPrd as RptCR, G3DR.RptPrd as RptDR
FROM dbo.Product Prd
INNER JOIN dbo.ProductG1 G1CR
ON Prd.IDPrdG1CR = G1CR.IDPrdG1
INNER JOIN dbo.ProductG2 G2CR
ON G1CR.IDPrdG2 = G2CR.IDPrdG2
INNER JOIN dbo.ProductG3 G3CR
ON G2CR.IDPrdG3 = G3CR.IDPrdG3
INNER JOIN dbo.ProductG1 G1DR
ON Prd.IDProdG1DR = G1DR.IDPrdG1
INNER JOIN dbo.ProductG2 G2DR
ON G1DR.IDPrdG2 = G2DR.IDPrdG2
INNER JOIN dbo.ProductG3 G3DR
ON G2DR.IDPrdG3 = G3DR.IDPrdG3
WHERE G3DR.RptPrd = 1
OR G3CR.RptPrd = 1Based on the above script I don't see all the products because the debit ID is related to a non-reporting ID. I need to be able to show all the reporting ID's and exclude the non-reporting ID's.
Any help at all would be great.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
Please clarify re don't see all the products. When I run the sql I get one line per row in Table - Product.
Can u list the results from your test using the data you supplied and what you would like as a result.
ps I had to correct your sql to make it work, please supply your working sql as well.
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
Please clarify re don't see all the products. When I run the sql I get one line per row in Table - Product.Can u list the results from your test using the data you supplied and what you would like as a result.
ps I had to correct your sql to make it work, please supply your working sql as well
Sorry, I mixed up my table hierarchy. I always get caught with that because we have the highest number as the top of the hierarchy. Makes it's a real nightmare to add levels, which we've only done once so far thankfully.
The tables shoudl be populated as follows,
Table Product
IDPrdIDPrdG1CRIDPrdG1DR
L1 11936 11938
L2 11936 11938
L3 11937 11939
L4 11936 11938
L5 1193711939
L6 11937 11939
Table ProductG1
IDPrdG1IDPrdG2
11936 20513
11937 20514
11938 20515
11939 20516
Table ProductG2
IDPrdG2IDPrdG3
20513 30452
20514 30453
20515 30454
20516 30455
Table ProductG3
IDPrdG3RptPrd
304521
30453 1
30454 1
30455 0
This means that when the query is executed I get,
IDPrd IDPrdG1CR IDPrdG2CR IDPrdG3CR IDPrdG1DR IDPrdG2DR IDPrdG3DR RptCR RptDR
----- ----------- ----------- ----------- ----------- ----------- ----------- ----- -----
L1 11936 20513 30452 11938 20515 30454 1 1
L2 11936 20513 30452 11938 20515 30454 1 1
L3 11937 20514 30453 11939 20516 30455 1 0
L4 11936 20513 30452 11938 20515 30454 1 1
L5 11937 20514 30453 11939 20516 30455 1 0
L6 11937 20514 30453 11939 20516 30455 1 0
The problem with this is that I shouldn't be dsiplaying the ID's related to 30455 because it's flagged as non-reporting. However, I should display the ID's related to 30453 because it is reporting.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface -
You can use case statements to remove unwanted data and replace with zeros or as in the following blanks
SELECT Prd.IDPrd,
(CASE WHEN G3CR.RptPrd = 1 THEN CONVERT(varchar,G1CR.IDPrdG1) ELSE '' END) as IDPrdG1CR,
(CASE WHEN G3CR.RptPrd = 1 THEN CONVERT(varchar,G2CR.IDPrdG2) ELSE '' END) as IDPrdG2CR,
(CASE WHEN G3CR.RptPrd = 1 THEN CONVERT(varchar,G3CR.IDPrdG3) ELSE '' END) as IDPrdG3CR,
(CASE WHEN G3DR.RptPrd = 1 THEN CONVERT(varchar,G1DR.IDPrdG1) ELSE '' END) as IDPrdG1DR,
(CASE WHEN G3DR.RptPrd = 1 THEN CONVERT(varchar,G2DR.IDPrdG2) ELSE '' END) as IDPrdG2DR,
(CASE WHEN G3DR.RptPrd = 1 THEN CONVERT(varchar,G3DR.IDPrdG3) ELSE '' END) as IDPrdG3DR,
G3CR.RptPrd as RptCR,
G3DR.RptPrd as RptDR
FROM #Product Prd
INNER JOIN #ProductG1 G1CR ON Prd.IDPrdG1CR = G1CR.IDPrdG1
INNER JOIN #ProductG2 G2CR ON G1CR.IDPrdG2 = G2CR.IDPrdG2
INNER JOIN #ProductG3 G3CR ON G2CR.IDPrdG3 = G3CR.IDPrdG3
INNER JOIN #ProductG1 G1DR ON Prd.IDPrdG1DR = G1DR.IDPrdG1
INNER JOIN #ProductG2 G2DR ON G1DR.IDPrdG2 = G2DR.IDPrdG2
INNER JOIN #ProductG3 G3DR ON G2DR.IDPrdG3 = G3DR.IDPrdG3
WHERE G3DR.RptPrd = 1OR G3CR.RptPrd = 1
Far away is close at hand in the images of elsewhere.
Anon. -
Or, if you can produce the data in singles using some form of CR/DR id, e.g.
SELECT Prd.IDPrd, 'CR' AS 'CRDR', G1CR.IDPrdG1, G2CR.IDPrdG2,G3CR.IDPrdG3
FROM #Product Prd
INNER JOIN #ProductG1 G1CR ON Prd.IDPrdG1CR = G1CR.IDPrdG1
INNER JOIN #ProductG2 G2CR ON G1CR.IDPrdG2 = G2CR.IDPrdG2
INNER JOIN #ProductG3 G3CR ON G2CR.IDPrdG3 = G3CR.IDPrdG3 AND G3CR.RptPrd = 1
UNION
SELECT Prd.IDPrd, 'DR' AS 'CRDR', G1DR.IDPrdG1,G2DR.IDPrdG2,G3DR.IDPrdG3
FROM #Product Prd
INNER JOIN #ProductG1 G1DR ON Prd.IDPrdG1DR = G1DR.IDPrdG1
INNER JOIN #ProductG2 G2DR ON G1DR.IDPrdG2 = G2DR.IDPrdG2
INNER JOIN #ProductG3 G3DR ON G2DR.IDPrdG3 = G3DR.IDPrdG3 AND G3DR.RptPrd = 1
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply