January 21, 2014 at 12:33 pm
Hi all,
I am trying to create a query on two tables (master and detail). I am struggling with a solution for what I thought was a trivial problem, hopefully it actually is.
Summary:
1. The master (fact) table contains:
- surrogate keys to dimension tables.
- Measures (values) as well as user defined attributes
- The master table is configurable. That is to say that the number of dimensions, measures and attributes are defined by an admin, so it is not predictable.
2. The detail table contains:
- A reference to the factId in the master table
- predetermined fields which do not change
3. The goal is to query the master and detail information. The challenge is to join the detail transactions to the master table, so that the detail transaction results include the dimensions and attributes of the referenced master transaction.
Example:
MASTER (FACT)
Iddim0dim1dim2value1value2descriptionUDF1
99910200400550.2 700.5 test row 1West
DETAIL
idFactIdvalue description
1999 300 detail row 1
2999 200 detail row 2
3999 50.2 detail row 3
DESIRED RESULT
dim0dim1dim2value1value2description UDF1
10200400550.2 700.5 test row 1 West((MASTER))
10200400300 0 detail row 1 West((DETAIL))
10200400200 0 detail row 2 West((DETAIL))
1020040050.2 0 detail row 3 West((DETAIL))
I know I can combine the transactions with a UNION but I am struggling with a method to join the detail to the master so that the Value from the Detail table is used for the detail transactions. I have tried joins, cross joins, cross apply but can’t seem to find a way to do it without dynamic SQL. I need a simple Select for this task.
Detailed example:
Create Table PTMaster
(
Idint
, dim0int
, dim1int
, dim2 int
, value1numeric (28,10)
, value2numeric (28,10)
, description varchar(254)
, UDF1 varchar(50))
Create Table PTDetail
(
id int
, FactId int
, value numeric (28,10)
, description varchar(254))
Insert into PTMaster (Id,dim0,dim1,dim2,value1,value2,description,UDF1)
Values(999,10,200,400,550.2,700.5,'test row 1','West')
Insert into PTDetail(ID, FactId,value,description)
Values(1,999,300,'detail row 1')
,(2,999,200,'detail row 2')
,(3,999,50.1,'detail row 3')
As a side note, in the concept, there is a single detail table which is used for multiple master tables. So I cannot simply write all dimension and attribute info to the detail tables. I try to avoid duplication anyway but need to consider all options if a 'Select...' is not possible.
Any ideas?
Thanks,
Mike
January 21, 2014 at 12:54 pm
Remember that a query is like a table definition. It defines the columns returned. You are wanting different columns to appear in a given row. About the only way you are going to pull this off is with a UNION. You want one row with the parent details and any number of additional rows for each child.
If you can post ddl and sample data in a consumable format we can help you figure out something. Please take a few minutes to read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2014 at 1:15 pm
Do you need something like this? I think I'm missing something.
SELECT Id,
dim0,
dim1,
dim2,
value1,
value2,
description,
UDF1
FROM PTMaster
UNION ALL
SELECT M.Id,
M.dim0,
M.dim1,
M.dim2,
D.value AS value1,
0 AS value2,
D.description,
M.UDF1
FROM PTMaster M
JOIN PTDetail D ON M.Id = D.FactId
January 21, 2014 at 1:19 pm
Thanks for the reply Sean. I posted example tables and data in the original message, which is consumable. I did not comment inline but it is very basic. I realize I also posted samples which the post specifically says is a big "don't." However, in an effort to mitigate a back and forth conversation just to get to the starting point, I thought it best to provide as much info as possible, albeit a bit garbled looking. Certainly would help if the forum had the ability to create a table format. 🙂
In any event, certainly the Union is required to produce a result with both master and detail info. The challenge is really on the detail side of the union. I need to join to the master to inherit the columns. However, since TSQL does not have a method to exclude columns when using the * syntax, I am wondering if there is any solution to essentially replace the value from the master transaction with the value from the detail transaction. I am certain there is a better way to explain this but best to see the example. Any additional thoughts?
January 21, 2014 at 1:23 pm
Thanks Luis. Normally this would be perfect. However, in this case I cannot specify the columns. In one database, there may be 10 dimensions and 10 UDF attributes in the master and in another there may be 3 dimensions and no attributes in the master. The SELECT cannot be changed because of the nature of the application being deployed. So with that said, I need to either find a way to do a Select * , in which case I cannot use the AS for certain columns or use dynamic SQL to read the schema first, which I would like to avoid here. Hope that makes sense...
M
January 21, 2014 at 1:29 pm
@Mike (1/21/2014)
Thanks for the reply Sean. I posted example tables and data in the original message, which is consumable. I did not comment inline but it is very basic. I realize I also posted samples which the post specifically says is a big "don't." However, in an effort to mitigate a back and forth conversation just to get to the starting point, I thought it best to provide as much info as possible, albeit a bit garbled looking. Certainly would help if the forum had the ability to create a table format. 🙂In any event, certainly the Union is required to produce a result with both master and detail info. The challenge is really on the detail side of the union. I need to join to the master to inherit the columns. However, since TSQL does not have a method to exclude columns when using the * syntax, I am wondering if there is any solution to essentially replace the value from the master transaction with the value from the detail transaction. I am certain there is a better way to explain this but best to see the example. Any additional thoughts?
My apologies. There they are all nicely formatted and everything. I somehow managed to miss that huge block in your code entirely. :blush:
You are absolutely correct to provide as much detail as possible. There was nothing wrong with your post, the problem lies squarely between my chair and the keyboard in front of me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2014 at 1:31 pm
@Mike (1/21/2014)
Thanks Luis. Normally this would be perfect. However, in this case I cannot specify the columns. In one database, there may be 10 dimensions and 10 UDF attributes in the master and in another there may be 3 dimensions and no attributes in the master. The SELECT cannot be changed because of the nature of the application being deployed. So with that said, I need to either find a way to do a Select * , in which case I cannot use the AS for certain columns or use dynamic SQL to read the schema first, which I would like to avoid here. Hope that makes sense...M
If you have changing columns like this I don't how you are going to avoid dynamic sql. In fact, this is probably a very solid argument for why you should use it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply