Issue with LEFT JOIN

[Tortelloni]

Hi there

I've an issue with a query over 2 tables.

Table 1 looks like:
Name: Basis
Attributes: KTR (Number), KST (Number), YTD (Number), HR (Number)

Table 2:
Name: Budget
Attributes: KTR (Number), PartnerID (Number), Jan(Number)

Example values for Table Basis:
KTR: 370050
KST: 333000, 334000, 335000, 336000
YTD: 10, 20, 30, 40
HR: 1000, 2000, 3000, 4000

Example values for Table Budget:
KTR: 370050, 370050, 370050
PartnerID: 333000, 334000, 336000
Jan: 100, 200, 300

So what I expect when I execute the query :-) it should look like:
KTR ID YTD HR SumBud-Jan
370050 333000 10 1000 100
370050 334000 20 2000 200
370050 335000 30 3000
370050 336000 40 4000 300



Below what I tried so far:

SELECT
Basis.KTR,
Basis.KST,
Basis.YTD,
Basis.HR,
IIF(B.JS IS Null, 0, B.JS) AS JanSum
FROM
Basis
LEFT JOIN
(
SELECT
SUM([Jan]) AS SJ
FROM
Budget
GROUP BY [PartnerID]
) AS B
ON Basis.KST = B.[PartnerID]

Unfortunately it doesn't work properly. When I try to execute the query I get 2 popup windows an he is asking for param-values B.JS and B.PartnerID

Your help is much apprechiated :-)
Regards

[orange]

First thought I would change

IIF(B.JS IS Null, 0, B.JS) AS JanSum

to
IIF( IsNull(B.JS), 0, B.JS) AS JanSum
and try again

[Tortelloni]

thanks but is still asking for B.PartnerID ...

[orange]

Try

SELECT
Basis.KTR
, Basis.KST
, Basis.YTD
, Basis.HR
, IIF(IsNULL(B.JS), 0, B.JS) AS JanSum
FROM
Basis left JOIN
(SELECT PartnerId,
SUM([Jan]) AS JS
FROM
Budget 
GROUP BY [PartnerID]) as B
ON Basis.Kst = B.[PartnerID]

I got this with your data.

KTR
KST
YTD
HR
JanSum

370050
333000
10
1000
100

370050
334000
20
2000
200

370050
335000
30
3000
0

370050
336000
40
4000
300

[Tortelloni]

Try

SELECT
Basis.KTR
, Basis.KST
, Basis.YTD
, Basis.HR
, IIF(IsNULL(B.JS), 0, B.JS) AS JanSum
FROM
Basis left JOIN
(SELECT PartnerId,
SUM([Jan]) AS JS
FROM
Budget 
GROUP BY [PartnerID]) as B
ON Basis.Kst = B.[PartnerID]

I got this with your data.

KTR
KST
YTD
HR
JanSum

370050
333000
10
1000
100

370050
334000
20
2000
200

370050
335000
30
3000
0

370050
336000
40
4000
300

thanks a lot but, unfortunately it doesn't work properly because, KTR values in each table could be different. So in this case, he sum' the JanSum.
Try with the values as below:
Table Basis:

KTR	KST	YTD	HR
370050	333000	10	1000
370050	334000	20	2000
370050	335000	30	3000
370050	336000	40	4000
370051	333000	100	10000
370051	334000	200	20000
370051	335000	300	30000
370051	336000	400	40000
....

Table Budget

KTR	PartnerID	Jan
370050	333000	100
370050	334000	200
370050	336000	400
370051	333000	1000
370051	334000	2000
370051	335000	3000
...
...

So the result should be:

KTR	KST	YTD	HR	Jan
370050	333000	10	1000	100
370050	334000	20	2000	200
370050	335000	30	3000	0
370050	336000	40	4000	400
370051	333000	100	10000	1000
370051	334000	200	20000	2000
....

I don't know but probably a sum for Jan values are not required?

[orange]

Perhaps It's time you told readers what the issue is in plain English. WHAT are you trying to do? What do the terms mean?
I used your numbers and got the results you said "it should be" by adjusting your SQL.
Now, you say it doesn't work because when you use additional values the SQL doesn't give the results you expected.

Seems to me the "business problem" has not been properly defined.
Good luck with your project.

[lfpm062010]

Try this SQL. It matched your output.

SELECT Basis.KTR, Basis.KST, Basis.YTD, Basis.HR, IIf(IsNull([Budget].[Jan]),0,[Budget].[Jan]) AS Jan
FROM Basis LEFT JOIN Budget ON (Basis.KTR = Budget.KTR) AND (Basis.KST = Budget.PartnerID);

[Tortelloni]

ok in plain words...
those tables are 2 simplified SAP tables.
The first one (Basis) represents cost collectors (KTR) and account numbers (KST) who charged some amounts on KTRs yer-to-date (YTD) and full year (HR).
The second Table (Budget) represents the budgets for January (Jan) for each account (KST) on the respective cost collector (KTR).
Now it is possible we have some charges in the Basis table but without an appropriate budget in the Budget - table. (that's the reason for the 0-value)

So what I'm looking for is a query, which combines those 2 Tables so I can see for each KTR which KST has charged how much YTD and HR and what was the Budget (Jan) for it.

@ifpm: I tried your query but it asks me for param values....

Thanks a lot for your effort :-)

[Tortelloni]

I got it :-)

I've modified quite a bit your code ifpm and it works now as expected

SELECT Basis.KTR, Basis.KST, Basis.YTD, Basis.HR, sum(IIF(IsNull(Budget.Jan),0,Budget.Jan)) AS Jan
FROM Basis LEFT JOIN Budget ON (Basis.KST = Budget.PartnerID) AND (Basis.KTR = Budget.KTR)
GROUP BY Basis.KTR, Basis.KST, Basis.YTD, Basis.HR;

Thanks a lot community for your support!
Regards and enjoy

[orange]

Glad to hear you have it working. When joining tables it's important to get all fields necessary for your needs.
KTR and KST in your sample data.

Simplified SAP tables, but do you have the appropriate PKs defined on your tables?

Anyway, you're welcome. lpfm062010 and I are happy to help.