SQL Query with 2 joins and different values

user_17107141 0
Viewed 36

I'm quite the beginner so I suppose some of you would have an easy time on my task but I need some help:

I have 3 DBs. dbo_A_Personal, dbo_Z_Ferien and dbo_Z_ERFASSUNG

A_Pers has a Pers_ID (LPE_ID) that I can use to join Z_Ferien and Z_ERFASSUNG on.

In Z_Ferien I have 4 rows with that pers_ID and in Z_ERFASSUNG 96.

What I need is a result that has columns that are basically like that:

PersID Erf Fer
1224 5 0
1234 4 0
1234 6 0
1234 0 6

so far I have this:

SELECT dbo_A_PERSONAL.LPE_ID, dbo_Z_Ferien.ZFE_TAGE, dbo_Z_ERFASSUNG.ZER_Std100
FROM dbo_A_PERSONAL 
INNER JOIN dbo_Z_Ferien ON dbo_A_PERSONAL.LPE_ID = dbo_Z_Ferien.ZFE_LPE_ID 
INNER JOIN dbo_Z_ERFASSUNG ON dbo_A_PERSONAL.LPE_ID = dbo_Z_ERFASSUNG.ZER_LPE
WHERE dbo_A_PERSONAL.LPE_ID=804 AND dbo_Z_ERFASSUNG.ZER_EIGENSCH = 3;

I need that so I can sum up the value I need from Z_ERFASSUNG and Z_Ferien but I don't know how to make it so each value is only "printed" once.

I hope I explained it well enough so you guys can help me out.

sql
1 Answers

If I understand correctly an aggerate function is what you need here.

I added a sum function of both dbo_Z_Ferien & dbo_Z_ERFASSUNG, as well as adding a group by statement for LPE_ID. Which tells SQL to partition the sum only on LPE_ID

SELECT dbo_A_PERSONAL.LPE_ID, sum(dbo_Z_Ferien.ZFE_TAGE), sum(dbo_Z_ERFASSUNG.ZER_Std100)
FROM dbo_A_PERSONAL 
INNER JOIN dbo_Z_Ferien ON dbo_A_PERSONAL.LPE_ID = dbo_Z_Ferien.ZFE_LPE_ID 
INNER JOIN dbo_Z_ERFASSUNG ON dbo_A_PERSONAL.LPE_ID = dbo_Z_ERFASSUNG.ZER_LPE
WHERE dbo_A_PERSONAL.LPE_ID=804 AND dbo_Z_ERFASSUNG.ZER_EIGENSCH = 3
GROUP BY dbo_A_PERSONAL
user_80539750
Related