July 11, 2016 at 1:33 pm
Hello ,
In my system (CRM software) i have a definitions table which contains (tableID,valueIndex,valueDescription)
I am using this table to create a lot of drop down list values for fields on a page.
I have WorkOrders Table and WorkOrderFixPart Table.
When i store values in WorkOrder table or WorkOrderFixPart table i save only the valueIndex.
Later in other page i need to show a grid of details on the WorkOrder and WorkOrderFixPart and then i required to show the description of the valueIndex stored in WorkOrder or WorkOrderFixPart so for each value stored i need to create left outer join.
As the time passed there is a lot of fields and the left outer join are piles up.
looks something like this:
BenefitInput.dbo.tblTables AS DropDownListA1 ON wf.fldDropDownListA1 = DropDownListA1.fldValueID AND DropDownListA1.fldTableID = 101 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA3 ON wf.fldDropDownListA3 = DropDownListA3.fldValueID AND DropDownListA3.fldTableID = 103 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA4 ON wf.fldDropDownListA4 = DropDownListA4.fldValueID AND DropDownListA4.fldTableID = 104 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA5 ON wf.fldDropDownListA5 = DropDownListA5.fldValueID AND DropDownListA5.fldTableID = 105 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA6 ON wf.fldDropDownListA6 = DropDownListA6.fldValueID AND DropDownListA6.fldTableID = 106 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA7 ON wf.fldDropDownListA7 = DropDownListA7.fldValueID AND DropDownListA7.fldTableID = 107 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA13 ON wf.fldDropDownListA13 = DropDownListA13.fldValueID AND DropDownListA13.fldTableID = 168 LEFT OUTER JOIN
BenefitInput.dbo.tblTables AS DropDownListA14 ON wf.fldDropDownListA14 = DropDownListA14.fldValueID AND DropDownListA14.fldTableID = 175
When i run the view i don't know if i need all the definitions or which but statistically i maybe need only 30% of all the joins.
Has anyone have an idea how can i avoid so many joins? create dynamic view?
I hope someone will understand my description of the problem.
July 11, 2016 at 2:42 pm
July 12, 2016 at 12:43 am
Hi
Thank you drew.allen for a quick response.
I don't have an aggregate function to apply on the table for pivoting.
I took the time to create a basic example:
--===== Create the test table WorkOrder
CREATE TABLE #WorkOrderTable
(
fldWorkOrderID INT PRIMARY KEY CLUSTERED,
fldCustCode nvarchar(12),
fldDate smalldatetime,
fldValueId1 INT null,
fldValueId2 INT null,
fldValueId3 INT null
)
--===== Insert the test data into the test table WorkOrder
INSERT INTO #WorkOrderTable
(fldWorkOrderID, fldCustCode, fldDate, fldValueId1, fldValueId2,fldValueId3)
SELECT 1,'000000000121','July 12 2016 12:00AM',1,null,null UNION ALL
SELECT 2,'000000000122','July 12 2016 12:00AM',null,2,null UNION ALL
SELECT 3,'000000000123','July 12 2016 12:00AM',null,null,3 UNION ALL
SELECT 4,'000000000124','July 12 2016 12:00AM',null,null,null UNION ALL
SELECT 5,'000000000125','July 12 2016 12:00AM',null,null,null UNION ALL
SELECT 6,'000000000126','July 12 2016 12:00AM',1,2,3 UNION ALL
SELECT 7,'000000000127','July 12 2016 12:00AM',null,1,2 UNION ALL
SELECT 8,'000000000128','July 12 2016 12:00AM',2,3,null
--===== Create the test table WorkOrderFixPart
CREATE TABLE #WorkOrderFixPartTable
(
fldWorkOrderFixID INT PRIMARY KEY CLUSTERED,
fldWorkOrderID INT,
fldFixDate smalldatetime,
fldValueId11 INT null,
fldValueId21 INT null,
fldValueId31 INT null
)
--===== Insert the test data into the test table WorkOrderFixPart
INSERT INTO #WorkOrderFixPartTable
(fldWorkOrderFixID,fldWorkOrderID, fldFixDate, fldValueId11, fldValueId21,fldValueId31)
SELECT 1,1,'July 12 2016 12:00AM',1,null,null UNION ALL
SELECT 2,2,'July 12 2016 12:00AM',null,2,null UNION ALL
SELECT 3,3,'July 12 2016 12:00AM',null,null,3 UNION ALL
SELECT 4,4,'July 12 2016 12:00AM',null,null,null UNION ALL
SELECT 5,5,'July 12 2016 12:00AM',null,null,null UNION ALL
SELECT 6,6,'July 12 2016 12:00AM',1,2,3 UNION ALL
SELECT 7,7,'July 12 2016 12:00AM',null,1,2 UNION ALL
SELECT 8,8,'July 12 2016 12:00AM',2,3,null
--===== Create the test table tblTables
CREATE TABLE #tblTable(
fldTableID int NOT NULL,
fldValueID int NOT NULL,
fldValueDesc nvarchar(15) NULL,
CONSTRAINT [PK_tblTables] PRIMARY KEY NONCLUSTERED
(
fldTableID ASC,
fldValueID ASC
)
)
--===== Insert the test data into the test table WorkOrderFixPart
INSERT INTO #tblTable
(fldTableID,fldValueID, fldValueDesc)
SELECT 1,1,'problem1' UNION ALL
SELECT 1,2,'problem2' UNION ALL
SELECT 1,3,'problem3' UNION ALL
SELECT 2,1,'subproblem1' UNION ALL
SELECT 2,2,'subproblem2' UNION ALL
SELECT 2,3,'subproblem3' UNION ALL
SELECT 3,1,'section1' UNION ALL
SELECT 3,2,'section2' UNION ALL
SELECT 3,3,'section3' UNION ALL
SELECT 11,1,'fixResult1' UNION ALL
SELECT 11,2,'fixResult1' UNION ALL
SELECT 11,3,'fixResult1' UNION ALL
SELECT 21,1,'fixReason1' UNION ALL
SELECT 21,2,'fixReason2' UNION ALL
SELECT 22,3,'fixReason3' UNION ALL
SELECT 31,1,'fixTool1' UNION ALL
SELECT 31,2,'fixTool2' UNION ALL
SELECT 31,3,'fixTool3'
This are the tables involved in the view which the code will look like:
select wo.fldWorkOrderID, fldWorkOrderFixID,
fldValueId1,Id1Table.fldValueDesc as fldValue1Desc ,
fldValueId2,Id2Table.fldValueDesc as fldValue2Desc,
fldValueId3,Id3Table.fldValueDesc as fldValue3Desc,
fldValueId11, Id11Table.fldValueDesc as fldValue11Desc,
fldValueId21,Id21Table.fldValueDesc as fldValue21Desc,
fldValueId31,Id31Table.fldValueDesc as fldValue31Desc
from #WorkOrderTable wo inner join
#WorkOrderFixPartTable wf
on wo.fldWorkOrderID = wf.fldWorkOrderID LEFT OUTER JOIN
#tblTable AS Id1Table
ON wo.fldValueId1 = Id1Table.fldValueID AND Id1Table.fldTableID = 1
LEFT OUTER JOIN
#tblTable AS Id2Table
ON wo.fldValueId2 = Id2Table.fldValueID AND Id2Table.fldTableID = 2
LEFT OUTER JOIN
#tblTable AS Id3Table
ON wo.fldValueId3 = Id3Table.fldValueID AND Id3Table.fldTableID = 3
LEFT OUTER JOIN
#tblTable AS Id11Table
ON wf.fldValueId11 = Id11Table.fldValueID AND Id11Table.fldTableID = 11
LEFT OUTER JOIN
#tblTable AS Id21Table
ON wf.fldValueId21 = Id21Table.fldValueID AND Id21Table.fldTableID = 21
LEFT OUTER JOIN
#tblTable AS Id31Table
ON wf.fldValueId31 = Id31Table.fldValueID AND Id31Table.fldTableID = 31
Now because i have 3 columns in WorkOrderTable (fldValueId1, fldValueId2,fldValueId3)
and 3 columns in WorkOrderFixPartTable (fldValueId11, fldValueId21,fldValueId31)
which i want to show their Description in the view i need to create 6 LEFT OUTER JOINS.
In my real tables in the system i have many columns like this in both tables so the LEFT OUTER JOINS are piling up.
Any suggestions how can a retrieve the ValueDescs more efficiently ?
July 12, 2016 at 6:40 am
In my real tables in the system i have many columns like this in both tables so the LEFT OUTER JOINS are piling up.
Answer would not be acceptable for you, But Yes you can, Only if you place the Actual value i-e "problem1" instead of placing the LookupValueKey.
Otherwise, it is a simple answer you cannot. WHY???? In simple words, in a RDBMS if you want to join multiple lookup table with a single table you would be ending up in a same situation.
Any suggestions how can a retrieve the ValueDescs more efficiently ?
you can check the following What is so bad about EAV, anyway?
In the end, You have to see what is beneficial for you and for your application.
July 12, 2016 at 7:56 am
kobi.burkis (7/12/2016)
HiThank you drew.allen for a quick response.
I don't have an aggregate function to apply on the table for pivoting.
You actually do. If you can guarantee a unique value, both MAX() and MIN() will return that unique value. Because you have a primary key on #tblTable(fldTableID, fldValueID), you CAN guarantee that it's going to be unique.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 12, 2016 at 9:06 am
drew.allen (7/12/2016)
kobi.burkis (7/12/2016)
HiThank you drew.allen for a quick response.
I don't have an aggregate function to apply on the table for pivoting.
You actually do. If you can guarantee a unique value, both MAX() and MIN() will return that unique value. Because you have a primary key on #tblTable(fldTableID, fldValueID), you CAN guarantee that it's going to be unique.
Drew
+1
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2016 at 9:48 am
I would say that you're stuck with the 6 outer joins.
The problem is that you're joining on different columns from your WorkOrders table.
July 12, 2016 at 10:21 am
Forget what I said earlier, you should be able to do it, you just need to unpivot one table.
SELECT wo.fldWorkOrderID,
wf.fldWorkOrderFixID,
fldValueId1,
MAX( CASE WHEN it.fldTableID = 1 THEN it.fldValueDesc END) AS fldValue1Desc,
fldValueId2,
MAX( CASE WHEN it.fldTableID = 2 THEN it.fldValueDesc END) AS fldValue2Desc,
fldValueId3,
MAX( CASE WHEN it.fldTableID = 3 THEN it.fldValueDesc END) AS fldValue3Desc,
fldValueId11,
MAX( CASE WHEN it.fldTableID = 11 THEN it.fldValueDesc END) AS fldValue11Desc,
fldValueId21,
MAX( CASE WHEN it.fldTableID = 21 THEN it.fldValueDesc END) AS fldValue21Desc,
fldValueId31,
MAX( CASE WHEN it.fldTableID = 31 THEN it.fldValueDesc END) AS fldValue31Desc
FROM #WorkOrderTable wo
inner join #WorkOrderFixPartTable wf on wo.fldWorkOrderID = wf.fldWorkOrderID
CROSS APPLY ( VALUES (1 , fldValueId1 ),
(2 , fldValueId2 ),
(3 , fldValueId3 ),
(11, fldValueId11),
(21, fldValueId21),
(31, fldValueId31))u(TableID, ValueID)
LEFT JOIN #tblTable AS it ON u.ValueID = it.fldValueID AND u.TableID = it.fldTableID
GROUP BY wo.fldWorkOrderID,
wf.fldWorkOrderFixID,
fldValueId1,
fldValueId2,
fldValueId3,
fldValueId11,
fldValueId21,
fldValueId31;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply