MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2010

RE: SQL Join - all rows from any table for employee



fixed

Brilliant! Yes, I see why that would work, and I'll test the concept as
soon as I get to work! Very nice, thank you!

I am also surprised by the syntax on the CTE named Z. Looks like the syntax
for a View; didn't know that could be done, but I like it.

Thanks again, Birgitta!
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"Politics is not a bad profession. If you succeed there are many rewards, if
you disgrace yourself you can always write a book."
-- Ronald Reagan


Here is an example (a little simplified), just to see if I understood
clearly:
1. Table PRMAST:
Create Table Qtemp/PRMAST
(employee Integer,
name Varchar(50));

Insert into PRMAST
Values (1, 'Hauser'), (2, 'Meier'), (3, 'Schmidt'), (4, 'Huber'), (5,
'Fischer');

Results in:
Empl Name
1 Hauser
2 Meier
3 Schmidt
4 Huber
5 Fischer

2. Table DEDBAL
Create Table Qtemp/DEDBAL
(employee Integer,
DedAmount Dec(11, 2),
DedOther Dec(11, 2));

insert into DEDBAL
Values (1, 123,45, 100,00), (3, 555,20, 700,3), (4, 222,0, 200,0);

Results in:
Empl DedAmount DedOther
1 123,45 100,00
3 555,20 700,30
4 222,00 200,00

3. Table INCBAL
Create Table QTEMP/INCBAL
(employee Integer,
IncAmount Dec(11, 2),
IncOther Dec(11, 2));;

Insert into Incbal
Values (2, 100,45, 300,22), (3, 200,2, 0), (4, 100,3, 0);

Results in:
Empl IncAmount IncOther
2 100,45 300,22
3 200,20 0,00
4 100,30 0,00

Now let's put all together:
With x as (Select Employee From DEDBAL
Union
Select Employee From INCBAL),
y as (select p.*
from x join prmast p on x.employee = p.employee),
z (Employee, Name, DedAmount, DedOther, IncAmount, IncOther)
as (Select y.employee, name, DedAmount, DedOther, 0, 0
From y left outer join DedBal d on y.employee = d.employee
Union
Select y.employee, name, 0, 0, IncAmount, IncOther
From y left outer join IncBal i on y.employee = i.employee)
Select Employee, Name,
Sum(DedAmount), Sum(DedOther), Sum(IncAmount), sum(IncOther)
From z
Group by Employee, Name;;

The query above results in:
Empl Name DedAmount DedOther IncAmount
IncOther
1 Hauser 123,45 100,00 0,00 0,00
2 Meier 0,00 0,00 100,45 300,22
3 Schmidt 555,20 700,30 200,20 0,00
4 Huber 222,00 200,00 100,30 0,00

CTE x: Determines all employees that are either in DEDBAL or INCBAL
CTE y: Joins the first CTE with PRMAST, to get all information needed
from
PRMAST for the selected employees
CTE z: Joins CTE y with DEDBAL and INCBAL and combines the results in a
single table
Final select summarizes the results from z

BTW UNION and UNION DISTINCT is the same, i.e. duplicates are ignored
If you need all rows from both tables you need to specify UNION ALL

@Eric: Common Table Expressions are NOT temporary tables, but temporary
views that are only available for the current query.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dennis Lovelady
Gesendet: Friday, 11. June 2010 00:31
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL Join - all rows from any table for employee

Yeah, I was pretty loose about the specifics because I don't want to
give
the impression that I want someone else to write this for me. I don't.
But
here we go:

DEDBAL contains quarterly deduction amounts (employer and employee
contribution), the year, the quarter, employee number and other stuff.

INCBAL contains quarterly income amounts, Federal wage base, the year,
the
quarter, employee and other stuff.

I need employee name, year, quarter, max(wage base), sum(income
amounts),
sum(employee contribution), sum(employer contribution) and some other
stuff.

So from these three tables, I need multiple columns of each, put
together
onto a single row by employee.

UNION might work, but I don't think UNION DISTINCT (by itself) will do
what
I want. I could do a left outer and an exception join, but then the
summing
and other details would get complex and would need to be mostly (but
not
completely) duplicated. Messy.

I liked the look of FULL JOIN, but apparently that's only available
some
after V5R3, which is where we are.

Likely I'll just write a program, but I wondered if there might be a
more
standard solution.

Sorry I wasn't completely upfront before. I just don't want to be one
of
those "here's my task - do it for me" people.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"A cynic is a man who knows the price of everything and the value of
nothing."
-- Oscar Wilde

Do you need info from DEDBAL and INCBAL? If not, you could use
EXISTS.

SELECT * <-- desired fields from PRMAST go here
FROM PRMAST
WHERE EXISTS (SELECT 1 FROM DEDBAL WHERE DEDEMP=PREMP)
OR EXISTS (SELECT 1 FROM INCBAL WHERE INCEMP=PREMP)


If you need to pull data from DEDBAL and INCBAL, you could use a LEFT
OUTER
join
and then exclude those cases where an employee has no records in
DEDEMP
and
INCEMP.

Try this:

SELECT * <-- desired fields from PRMAST, DEDBAL, and INCBAL go here
FROM PRMAST
LEFT OUTER JOIN DEDBAL ON DEDEMP=PREMP
LEFT OUTER JOIN INCBAL ON INCEMP=PREMP
WHERE DEDEMP IS NOT NULL OR INCEMP IS NOT NULL

In this case, if an employee has records in DEDBAL but not in INCBAL,
any
fields in INCBAL would be null and vice versa.

Have fun!

Richard Casey




-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Thursday, June 10, 2010 3:38 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL Join - all rows from any table for employee

We have three tables: DEDBAL, INCBAL, and PRMAST.



Each of these contains an employee number (DEDEMP, INCEMP, PREMP).
We
want
to produce output where an employee is represented in either DEDBAL
and
INCBAL or both. (For the sake of argument, we can assume that PRMAST
is
present for each employee).



I've tried various types of joins (LEFT OUTER, FULL, et cetera) and
have
pored through the archives and GOOGLE and come up empty on a means of
doing
this exactly right. (I'm convinced this is a common issue; so my
issue
is
probably my choice of search words - usually resulting in too many
hits).
Would some kind soul mind directing me to guidelines on how to write
the
JOIN for this challenge?



Thanks!



Dennis E. Lovelady
AIM/Skype: delovelady MSN: fastcounter@xxxxxxxxxxxx
<http://www.linkedin.com/in/dennislovelady>
www.linkedin.com/in/dennislovelady --
I am at one with my duality.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact