Just as a learning exercise

  • From: Thomas Day <tomdaytwo@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 4 May 2011 09:10:11 -0400

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.

Other related posts: