SQLTeam.com | Weblogs | Forums

SQL table in EXCEL with union and join problems


#1

Hello,

First of all i hope i selected the right topic.

I am very new to SQL and I am trying to combine 3 tables:

T1 and T2 are the same. I managed to combine those to by union all (place below each other).

Now T3 is a problem. I am trying to add a client name from t3 (t3.debiteur) to the union table IF:

"uniontables".ordersoort = t3.ordersoort AND "uniontables".project = t3.project AND ISNULL("uniontables".'Bedrag VC',0) + ISNULL("uniontables".'Toeslag VC',0) = t3.Bedrag

I tried it as follows (doesnt work):

Select
T1.Productherkenning,
T1.Project,
T1.Ordernummer as [Ordernumber],
T3.debiteur,
T1.Orderstatus,
isnull(T1.[Bedrag VC],0)+isnull(T1.[Toeslag VC],0) AS 'VC (FVP)'
FROM I_TN_Exp_FCC_costprice_project_001 T1
join I_TN_Exp_SLS_tdsls041_001_1 T3 on T1.ordernummer = T3.ordernummer AND T1.project,6 = T3.project,6 AND isnull('T1.Bedrag VC',0)+isnull(T1. 'Toeslag VC',0) = T3.Bedrag
WHERE left(T1.productherkenning,3)='018'

Union ALL

Select
T2.Productherkenning,
T2.Project,
T2.Ordernummer as [Ordernumber],
T3.debiteur,
T2.Orderstatus,
isnull(T2.[Bedrag VC],0)+isnull(T2.[Toeslag VC],0) AS 'VC (FVP)'
FROM I_TN_Exp_FCC_costprice_project_001 T2
join I_TN_Exp_SLS_tdsls041_001_1 T3 on T2.ordernummer = T3.ordernummer AND T2.project,6 = T3.project,6 AND isnull('T2.Bedrag VC',0)+isnull(T2. 'Toeslag VC',0) = T3.Bedrag
WHERE left(t2.productherkenning,3)='018'


#2
with cte as 
(
 -- put your union here
)

select <column list> from cte
join T3 on  cte.ordersoort = t3.ordersoort 
AND cte.project = t3.project 
AND ISNULL(cte.'Bedrag VC',0) + 
ISNULL(cte.'Toeslag VC',0) = t3.Bedrag