Just as a learning exercise
Date: Wed, 4 May 2011 09:10:11 -0400
Message-ID: <BANLkTin=rT2ZE_h2e0mpeggW0BYScnguRg_at_mail.gmail.com>
I've got a query in ANSI join format that returns all employees along with the required training courses and the ethics training. If they've completed a course then there will be a completed date in the training_survey table. If not, then the completed date will be blank, indicating that that employee needs to take that course. I have an ANSI join query that returns one row per employee per course, whether they've completed the course during the fiscal year or not.
The ANSI join looks like:
select p.last_name, p.first_name,s.title, t.created as completed
from persons p
join required_courses s on 1=1
left join training_survey t on s.ap_code = t.survey_app and t.user_id = p.id
and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
where s.type = 'survey'
union
select p.last_name, p.first_name, 'Ethics' AS title, created as completed
from persons p
left join ethics_training u on p.id = u.userid
and created between to_date('10/01/2009', 'MM/DD/YYYY')
and to_date('09/30/2010', 'MM/DD/YYYY')
and checkthis = 1
where 1=1
order by last_name, first_name;
My question is, what would with look like with the Oracle join (+)? I've
tried several variations but it doesn't return the rows were there is no
completed date within the fiscal year.
I want the Cartesian product of employees and courses. I can do that. The
problem is in translating
left join training_survey t on s.ap_code = t.survey_app and t.user_id = p.id and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY') and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
So that it pulls in a created date when one is available but, when one is not available, it still leaves the row in the result set.
It's not a problem; I'd just like to learn how to do that.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 04 2011 - 08:10:11 CDT