Easy join problem

[morechocolate]

I have two tables with data that looks something like the following:

LOAN_NO AMOUNT1 AMOUNT2
200 152.60 4569.00
203 1652.00 7896.00
205 2589.36 159.62

LOAN_NO EMPLOYEE POSITION START_DATE END_DATE
200 Chris J. Accountant 2001-02-11 NULL
200 Ed P. Analyst 1998-03-04 NULL
203 Kelly M. Analyst 1999-08-22 NULL
205 Jake H. Accountant 1985-02-03 NULL
206 Pat W. Accountant 1986-05-18 2004-01-31


I want all the loan numbers from the first table and from the second table I want all the accountants. Even if there is no accountant in the second table, I want the amount for all the loans in the first table.

When I do a left outer join like the following, I do not get Loan Number 203. I am having serious brain freeze on how to get it. It seems to be happening because the loan does exist in table two, but because of the accoutant criteria I am not getting it.

select t1.loan_number,t1.amount1, t1.amount2
from table1 t1
left outer join table2 t2 on
t1.loan = t2.loan
where (t2.postition = 'accountant' and
t2.end_date is null) or
t2.loan is null /* this does not do anything because loan number 203 is in table 2 */

Thanks

mc

 

[SQLSister]

does this do it?

select t1.loan_number,t1.amount1, t1.amount2, t2.position
from table1 t1
left outer join (select position, loan from table2 t3 where (t3.postition = 'accountant' and
             t3.end_date is null)  t2 on
 t1.loan = t2.loan

I threw inteh position part even thou it isn;t in your original query as i figured you wanted it to show at least as long as you are testing to see if you get the right data. In fact without showing it, I'm not sure why you need to join at all.

Questions about posting. See faq183-874

 

[dbomrrsm]

select t1.loan_number,t1.amount1, t1.amount2,
position = 
case t2.position WHEN 'Accountant' then t2.position else 'No Accountant'
end
from table1 t1, table2 t2

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

select t1.loan_number,t1.amount1, t1.amount2,
position = 
case t2.position WHEN 'Accountant' then t2.position else 'No Accountant'
end
from table1 t1, table2 t2

[blue]DBomrrsm[/blue]

 

[morechocolate]

Thank you both.

The one part of information that I left out is that I will need the phone number for each accountant along with the values. I believe that answers SQLsister's question.

I will give the code a try.

Thanks again.

 

[SQLSister]

Then add the phone number field to the derived table and the outer select list.

Questions about posting. See faq183-874

 

[ESquared]

Skip the derived table, put the accountant criterion in the join clause:

SELECT
      T1.Loan_No
      T1.Amount1
      T1.Amount2
   FROM T1
      LEFT JOIN T2 ON T1.Loan_No = T2.Loan_No AND T2.Position = 'Accountant'

If you have multiple accountants per loan number and you only want one, then you should use SQLSister's derived table method and NOT just toss in a DISTINCT. You would need to make the derived table a GROUP BY and use some method for determining which of the multiple accountants you'd want (first alphabetically, for example).

A potential problem I see with your current design is that each employee can only be associated with one Loan_No, unless you are duplicating data in the employees table which would be (in my opinion) a very bad idea. The solution is to create a join table, Loans_Employees, with one column listing the loan and one listing the employee. You might want a column to indicate what the relationship between the two is, especially if for example an employee could be both the accountant and the analyst for a loan. You also might want an identity column in the join table so you can identify and distinguish records more easily.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[morechocolate]

Thanks everyone.

I had actually already tried the method esquared mentioned, but totally forgot I was linking on one other table. I moved some statements around and did a right outer join and now I am fine.

In the end, the table I forgot about I put that as the table after the from then I joined table2 to that table using the statement that esquared had at the left outer join piece and finally I did a right outer join of table1 to table2.

Thanks Everyone

 

[ESquared]

Any comments on my comments on the table design?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[morechocolate]

Yes,

I wanted to let you know that this is a table that was designed by the vendor. Not too much I can do about that. I just have to work with the table design and get the correct information to the business unit.

I really appreciated your help and your feedback.