Two Left Outer Joins

[cards4]

Hello,
I am trying to join a table with a result set but I'm having difficulty doing it. In my SQL, I left outer join two tables and get results. I want to left outer join these results to another table. Is there a way for me to do that or should I put the results into a temp table and left outer join the third table with the temp table? Thank you.

 

[AlexCuse]

All you need to do is put parentheses around your query as it exists now and give it an alias. Then you can join it to other tables. If you post your query It'd be much easier to talk you through it.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[cards4]

This is what I have so far:

SELECT *,
length_of_service = case When rehire_date Is NULL
Then round(convert(decimal, datediff(dd, hire_date, getDate()))/365.2422,1)
ELSE round(convert(decimal, datediff(dd, rehire_date, getDate()))/365.2422,1)
END,
cost_center=right(rpt_empl_oper_dept.orgcode,4)
FROM rpt_empl_oper_dept
left outer join empper
on rpt_empl_oper_dept.emp_id=empper.emp_id
WHERE (oper IN ('????', '????', '????', '????'))
and pers_status='A'
ORDER BY oper, empper.emp_id

I want to left outer join this to another table. Please help!

The question marks are not the data in the table.

 

[AlexCuse]

So you would format your statement like this:

select a.somefield, a.anotherfield, b.somefield
from
(
SELECT *, 
length_of_service = case When rehire_date Is NULL 
Then round(convert(decimal, datediff(dd, hire_date, getDate()))/365.2422,1)
ELSE round(convert(decimal, datediff(dd, rehire_date, getDate()))/365.2422,1)
END,
cost_center=right(rpt_empl_oper_dept.orgcode,4)
FROM rpt_empl_oper_dept
left outer join empper
on rpt_empl_oper_dept.emp_id=empper.emp_id
WHERE (oper IN ('????', '????', '????', '????'))
and pers_status='A'
)
a
LEFT JOIN [b]someOtherTable b [/b]
on [b]a.someField = b.someField[/b]
ORDER BY a.oper, a.emp_id

NOtice that I moved your order by down to the bottom. This is because you cannot use order by in a derived table (which is what your initial query becomes when used like this).

Hope this helps you out,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[cards4]

Hello,
I just got back from Thanksgiving break so I was unable to use this code until now. When I implemented this code it gave me: "The column 'emp_id' was specified multiple times for 'a'." Could it because I did a left outer join in the second select statement?

 

[gmmastros]

No.

The problem is that empid exists in the 2 tables you are using for the inner query. Since you have Select * it is returning all the fields from both of those tables. Instead of select *, change it to select only those fields you need for this particular query, and make sure that you are only returning the empid column once.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

George is right, select * is a bad practice. You really shouldn't use it. Specify only the columns you actually need.

Questions about posting. See faq183-874

 

[cards4]

It worked! Thank you.