August 15, 2018 at 12:57 pm
I have two tables tab1 and tab2. Column CDCode on Tab2 table joins with two columns (credit and debit) on Tab1 table separately. One solution for me is to use Union ALL which works perfectly fine. Problem here is that, the database is OLTP database and actual tables have ~300k rows. To get real time data, I cannot use NoLock Hints.
Is there other alternative to have them in a single join without UNION ALL and get same oputput ?
DECLARE @TAB1 TABLE
(
ID TINYINT NOT NULL,
CREDIT SMALLINT NOT NULL,
DEBIT SMALLINT NOT NULL
)
INSERT @TAB1(ID,CREDIT,DEBIT )
VALUES
(1,1,2), (2,1,3), (3,3,2),(4,2,2)
/* Table2 has CDCode that contains both credit and debit values */
DECLARE @TAB2 TABLE
(
AdjId SMALLINT NOT NULL,
CDCode SMALLINT NOT NULL,
AdjName NVARCHAR(20) NOT NULL,
AdjType NVARCHAR(20) NOT NULL
)
INSERT @TAB2(AdjId,CDCode,AdjName,AdjType)
VALUES
(1,1,N'Current',N'Income'), (2,2,N'Deffered',N'Income'),(3,3,N'Prepaid',N'Expense')
/* Union ALL solution but this hits the same tables twice */
SELECT T2.CDCode, 'Credit' AS [Credit/Debit],T2.AdjName,T2.AdjType FROM @TAB1 AS T1
JOIN @TAB2 AS T2 ON T1.CREDIT=T2.CDCode
UNION ALL
SELECT T2.CDCode,'Debit' AS [Credit/Debit],T2.AdjName,T2.AdjType FROM @TAB1 AS T1
JOIN @TAB2 AS T2 ON T1.DEBIT=T2.CDCode
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 15, 2018 at 1:21 pm
Why exactly are you worried that it's hitting the same table twice? This seems like a fine use for a UNION.
August 15, 2018 at 1:31 pm
I believe a cross tab query should give you what you want:
Select
CROSSTAB.CDCCode,
CROSSTAB.[Credit/Debit],
CROSSTAB.AdjName,
CROSSTAB.AdjType
From @TAB1 As T1
Left Join @TAB2 As C On T1.CREDIT = C.CDCode
Left Join @TAB2 As D On T1.DEBIT = D.CDCode
Cross Apply (Values (C.CDCode, 'Credit', C.AdjName, C.AdjType),
(D.CDCode,'Debit', D.AdjName, D.AdjType))
As CROSSTAB(CDCCode, [Credit/Debit], AdjName, AdjType)
Where CROSSTAB.CDCCode Is Not Null
August 15, 2018 at 2:00 pm
ZZartin - Wednesday, August 15, 2018 1:21 PMWhy exactly are you worried that it's hitting the same table twice? This seems like a fine use for a UNION.
I received code review comments to avoid hitting same tables as much as I can.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 15, 2018 at 2:02 pm
andycadley - Wednesday, August 15, 2018 1:31 PMI believe a cross tab query should give you what you want:
Select
CROSSTAB.CDCCode,
CROSSTAB.[Credit/Debit],
CROSSTAB.AdjName,
CROSSTAB.AdjType
From @TAB1 As T1
Left Join @TAB2 As C On T1.CREDIT = C.CDCode
Left Join @TAB2 As D On T1.DEBIT = D.CDCode
Cross Apply (Values (C.CDCode, 'Credit', C.AdjName, C.AdjType),
(D.CDCode,'Debit', D.AdjName, D.AdjType))
As CROSSTAB(CDCCode, [Credit/Debit], AdjName, AdjType)
Where CROSSTAB.CDCCode Is Not Null
Thanks Andy, your solution works good.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 15, 2018 at 2:21 pm
Naveen PK - Wednesday, August 15, 2018 2:02 PMandycadley - Wednesday, August 15, 2018 1:31 PMI believe a cross tab query should give you what you want:
Select
CROSSTAB.CDCCode,
CROSSTAB.[Credit/Debit],
CROSSTAB.AdjName,
CROSSTAB.AdjType
From @TAB1 As T1
Left Join @TAB2 As C On T1.CREDIT = C.CDCode
Left Join @TAB2 As D On T1.DEBIT = D.CDCode
Cross Apply (Values (C.CDCode, 'Credit', C.AdjName, C.AdjType),
(D.CDCode,'Debit', D.AdjName, D.AdjType))
As CROSSTAB(CDCCode, [Credit/Debit], AdjName, AdjType)
Where CROSSTAB.CDCCode Is Not Null
Thanks Andy, your solution works good.
It still reads table 2 twice. Here is a solution that reads each table once.
SELECT T2.CDCode, cd.CDType, T2.AdjName, T2.AdjType
FROM @TAB1 AS T1
CROSS APPLY ( VALUES('Credit', T1.CREDIT), ('Debit', T1.DEBIT) ) cd(CDType, CDCode)
INNER JOIN @TAB2 AS T2
ON cd.CDCode = T2.CDCode
ORDER BY cd.CDType, T2.AdjName, T2.AdjType
Drew
PS: Even though Andy called it a CROSS TAB, it's not. A cross tab is similar to a pivot. What he and I both did is more similar to the reverse, that is, an unpivot.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 15, 2018 at 2:43 pm
Fantastic Drew. That is what I am looking precisely.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply