Left Join Query Using Multiple Tables

[Barb59]

Hi,

Here's the problem... I need to identify the deduction amount of people within a predetermined population who have a 401(k) deduction, but I want to return ALL the population in the results even if they do not have a deduction.

The tables are...
EBase - holds master record information - one record per employee
EDeduct - holds employee level deduction records. May have multiple deductions per employee. The current record has a null end date.
Deduction - holds system level deduction information

The relationships between tables are...
EBase.Ebflxid = EDeduct.Deeflxideb
Deduction.dedflxid = EDeduct.Deeflxidded

I constructed the following query...
select b.ebpsid, b.ebflxid, b.ebclock, b.eblastname, b.ebfirstname, e.deeamount
from ebase b left join ededuct e on b.flxid = e.deeflxideb,
join deduction d on d.dedflxid = e.deeflxidded
where d.dedcode = '401K' and e.deedateend is null and b.ebflxid in(<list of values here>)

I'm running SQL Server 7 and when I run the query I get an error message stating &quot;Incorrect syntax near the keyword 'join'.&quot;

I've reviewed the syntax in a couple of books and am having trouble figuring out what I've done wrong. Any advice would be appreciated!

Thanks,
Barb

 

[swampBoogie]

You had an extra comma before the second join

select b.ebpsid, b.ebflxid, b.ebclock, b.eblastname, b.ebfirstname, e.deeamount
from ebase b left join ededuct e on b.flxid = e.deeflxideb
join deduction d on d.dedflxid = e.deeflxidded
where d.dedcode = '401K' and e.deedateend is null and b.ebflxid in(<list of values here>)

 

[Barb59]

Thanks for the sharp eyes. Boy do I feel like a bonehead! I'd read in one book that the joins had to be separated with commas, but didn't notice in another book that they were omitted. When I removed the offending comma the syntax of the query was fine.

But now there's a new problem... I inserted my list of values in the last condition of the where clause - there were 52 values. I expected to get a result set with 52 items including the employees who do not have a 401(k) deduction, but what I got instead was only the employees who have a deduction.

I though using the left join operator would force all 52 to be in the results set. Is the second join somehow the problem?

Thanks.

 

[swampBoogie]

Well, both your joins should be outer joins and the where clause is a problem also. You can't put any condition on the right table there as that will filter out the non matching rows (as they are null). You can move the condition to the on clause instead.

select b.ebpsid, b.ebflxid, b.ebclock, b.eblastname, b.ebfirstname, e.deeamount
from ebase b left join ededuct e on b.flxid = e.deeflxideb
left join deduction d
on d.dedflxid = e.deeflxidded and d.dedcode = '401K'
where e.deedateend is null and b.ebflxid in(<list of values here>)

 

[Barb59]

I reran using your version of the query; however, it has returned multiple rows of data for the employees.

I added d.dedcode to the results to try and figure out what it's doing. What I see is instances where d.dedcode = 401K and others where it's null. Based on what I know about this data, I can say with certainty that it's returning ALL deductions for employees.

I have not been able to determine if it's returning all 52 employees with one or many deductions, or just a subset of the 52 who do have a 401(k) deduction (and also returing their other active deductions).

It looks like what I need to do is revise the query to return all 52 employees in the result set and for the employees who have a 401(k) deduction, return the amount of the deduction.

Here's a sample of what I need:

Name: Deduction Amount:
James Smith NULL
June Cleaver 2
Bill Holmes 4
Patty Gee NULL

 

[tlbroadbent]

Barb,

Don't you just love the Source 500 database and tables? Move the test for e.deedateend is null to JOIN criteria of the first JOIN.

select
b.ebpsid, b.ebflxid, b.ebclock,
b.eblastname, b.ebfirstname, e.deeamount
from ebase b
left join ededuct e
on b.flxid = e.deeflxideb
and e.deedateend is null
left join deduction d
on d.dedflxid = e.deeflxidded
and d.dedcode = '401K'
where b.ebflxid in(<list of values here>) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Barb59]

Hi Terry,

I do love Source, oddly enough! I've been working with it for 4 years and even though it makes me nuts on a daily basis somehow I still keep coming back.

I ran your revised version of the query and it returned all 52 of the employees, but what it also has done is return all active deductions for the employees. I ended up with a result set of 202 rows and once I sorted through the data I determined that they're all there, plus their other active deductions.

This whole left join thing with more than two tables is not an easy concept - at least it's not for me! How can I modify the query further to eliminate any deductions that do not have a d.dedcode = '401K'?

Thanks,
Barb

 

[arrowhouse]

select
b.ebpsid, b.ebflxid, b.ebclock,
b.eblastname, b.ebfirstname, e.deeamount
from ebase b
left join ededuct e
on b.flxid = e.deeflxideb
and e.deedateend is null
/*this gives us all the empoyees
**with one all e columns as nulls if no ededuct records
**and one row per current deduction an employee has
*/
left join deduction d
on d.dedflxid = e.deeflxidded
/* here we are joining to get the deduction information
** for each ededuct record in the above set, left outer to
** preserve the employees with no ededuct records
*/

where b.ebflxid in(<list of values here>)
and ISNULL(d.dedcode,'401K') = '401K'

/*if d.decode is null it is an employee with no ededuct - **so keep the record in the selection by converting it **to '401K', otherwise only give us the rows with dedcode = **401K
*/

I think...

 

[Barb59]

Hi Arrowhouse,

I ran your query and it too returned 202 rows. I think it's the same dataset as what Terry's query returned

Additionally, I don't want to convert the 'NULL' value in d.dedcode to '401K'. I want to leave them alone so I can identify the subgroup who are not deferring into the 401(k) plan within the population of 52 highly comp'd employees.

Thanks for trying!
Barb

 

[tlbroadbent]

One minor change I should have made. The 2nd JOIN should be an INNER JOIN.

select
b.ebpsid, b.ebflxid, b.ebclock,
b.eblastname, b.ebfirstname, e.deeamount
from ebase b
left join ededuct e
on b.flxid = e.deeflxideb
and e.deedateend is null
inner join deduction d
on d.dedflxid = e.deeflxidded
and d.dedcode = '401K'
where b.ebflxid in(<list of values here>) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Barb59]

Terry,

Thanks for the suggestion.

This time the query only returned the employees who had an active 401(k) deduction. It's almost as though the left join from EBase to EDeduct is being ignored or overridden.

I managed to pull the data together another way, so please don't spend any more time messing with this. I appreciate your advice and willingness to help.

Barb

 

[tlbroadbent]

Try adding parentheses and moving the first JOIN criteria as follows.

select
b.ebpsid, b.ebflxid, b.ebclock,
b.eblastname, b.ebfirstname, e.deeamount
from ebase b
left (join ededuct e
inner join deduction d
on d.dedflxid = e.deeflxidded
and d.dedcode = '401K')
on b.flxid = e.deeflxideb
and e.deedateend is null
where b.ebflxid in (<list of values here>) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Barb59]

Terry,

Thank-you so much, that did the trick.

Barb

 

[golandsh]

use this:

from (ededuct e left join ebase b on b.flxid = e.deeflxideb)
left join deduction d on d.dedflxid = e.deeflxidded