SQL to combine required and completed training courses.

#11

"rkc" <rkc (AT) rochester (DOT) yabba.dabba.do.rr.bomb> wrote

Quote:

Considering the following tables is it possible to
write an SQL query that would result in rows that look like
(EmpID, CourseID, DateTaken) for each employee and each
required course in one result set?

1 4 8/12/2005
1 5 8/12/2005
1 6 NULL
2 6 7/7/2006
2 4 NULL

Employees(EmpID*, PosID)
Positions(PosID*)
Courses(CourseID*)
RequiredCourses(CourseID*, PosID*)
CompletedCourses(EmpID*, CourseID*, DateTaken*)

Best I can do is a resultset for a single employee
doing a Union query filtering the not completed
courses something like this:

SELECT ...
FROM CompletedCourses
...
UNION
SELECT ...
FROM RequiredCourses
...
WHERE CourseID NOT IN
(SELECT CourseID
FROM CompletedCourses
INNER JOIN Employees
ON Employee.EmpID =
CompletedCourse.EmpID)

Let's start out with a simpler problem, namely to produce just the first two
columns of your specified result.

Maybe

SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID

The fact that we are using a pair of foreign keys as the join condition
seems odd. But then again, it's a many to many relationship. Test the
above, to see that it works.

Now, if it does work, use it as a subquery

SELECT er.EmpID, er.CourseID, c.DateTaken
FROM
(SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID) er,
LEFT JOIN CompletedCourses ON er.EmpID = c.EmpID AND er.CourseID =
c.CourseID

Note that I never used the Positions table! It's still a good idea to have
that table for the sake of referential integrity.

HTH.

#12

David Cressey wrote:

Quote:

"rkc" <rkc (AT) rochester (DOT) yabba.dabba.do.rr.bomb> wrote in message
news:AQ1xg.8249$8j3.6667 (AT) twister (DOT) nyroc.rr.com...
Considering the following tables is it possible to
write an SQL query that would result in rows that look like
(EmpID, CourseID, DateTaken) for each employee and each
required course in one result set?

1 4 8/12/2005
1 5 8/12/2005
1 6 NULL
2 6 7/7/2006
2 4 NULL

Employees(EmpID*, PosID)
Positions(PosID*)
Courses(CourseID*)
RequiredCourses(CourseID*, PosID*)
CompletedCourses(EmpID*, CourseID*, DateTaken*)

Best I can do is a resultset for a single employee
doing a Union query filtering the not completed
courses something like this:

SELECT ...
FROM CompletedCourses
...
UNION
SELECT ...
FROM RequiredCourses
...
WHERE CourseID NOT IN
(SELECT CourseID
FROM CompletedCourses
INNER JOIN Employees
ON Employee.EmpID =
CompletedCourse.EmpID)

Let's start out with a simpler problem, namely to produce just the first two
columns of your specified result.

Maybe

SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID

The fact that we are using a pair of foreign keys as the join condition
seems odd. But then again, it's a many to many relationship. Test the
above, to see that it works.

Now, if it does work, use it as a subquery

SELECT er.EmpID, er.CourseID, c.DateTaken
FROM
(SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID) er,
LEFT JOIN CompletedCourses ON er.EmpID = c.EmpID AND er.CourseID =
c.CourseID

Note that I never used the Positions table! It's still a good idea to have
that table for the sake of referential integrity.

HTH.

Hm, apart from terminological differences, a few erroneous commas and a
missing alias definition, I can't see any difference between this and
the answer already submitted.

Puzzled.

#13

<zac.carey (AT) gmail (DOT) com> wrote

Quote:

David Cressey wrote:
"rkc" <rkc (AT) rochester (DOT) yabba.dabba.do.rr.bomb> wrote in message
news:AQ1xg.8249$8j3.6667 (AT) twister (DOT) nyroc.rr.com...
Considering the following tables is it possible to
write an SQL query that would result in rows that look like
(EmpID, CourseID, DateTaken) for each employee and each
required course in one result set?

1 4 8/12/2005
1 5 8/12/2005
1 6 NULL
2 6 7/7/2006
2 4 NULL

Employees(EmpID*, PosID)
Positions(PosID*)
Courses(CourseID*)
RequiredCourses(CourseID*, PosID*)
CompletedCourses(EmpID*, CourseID*, DateTaken*)

Best I can do is a resultset for a single employee
doing a Union query filtering the not completed
courses something like this:

SELECT ...
FROM CompletedCourses
...
UNION
SELECT ...
FROM RequiredCourses
...
WHERE CourseID NOT IN
(SELECT CourseID
FROM CompletedCourses
INNER JOIN Employees
ON Employee.EmpID =
CompletedCourse.EmpID)

Let's start out with a simpler problem, namely to produce just the first
two
columns of your specified result.

Maybe

SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID

The fact that we are using a pair of foreign keys as the join condition
seems odd. But then again, it's a many to many relationship. Test the
above, to see that it works.

Now, if it does work, use it as a subquery

SELECT er.EmpID, er.CourseID, c.DateTaken
FROM
(SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID) er,
LEFT JOIN CompletedCourses ON er.EmpID = c.EmpID AND er.CourseID =
c.CourseID

Note that I never used the Positions table! It's still a good idea to
have
that table for the sake of referential integrity.

HTH.

Hm, apart from terminological differences, a few erroneous commas and a
missing alias definition, I can't see any difference between this and
the answer already submitted.

Puzzled.

You are right about the erroneous commas, etc.

But, it looks different to me from the answer aleady submitted. Fix it up,
try it out, and tell us.

The previous answer didn't do a LEFT JOIN against a single table, unless I
misread it. That could be why the SQL parser barfed at it.

#14

David Cressey wrote:>

Quote:

Let's start out with a simpler problem, namely to produce just the first two
columns of your specified result.

Maybe

SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID

The fact that we are using a pair of foreign keys as the join condition
seems odd. But then again, it's a many to many relationship. Test the
above, to see that it works.

Now, if it does work, use it as a subquery

SELECT er.EmpID, er.CourseID, c.DateTaken
FROM
(SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID) er,
LEFT JOIN CompletedCourses ON er.EmpID = c.EmpID AND er.CourseID =
c.CourseID

Note that I never used the Positions table! It's still a good idea to have
that table for the sake of referential integrity.

Thanks to both for the continuing effort on this.

This query runs in an instant and so close to the result I am
after that it hurts.

What is happening now is that when an employee has taken a course
more than once (certification expires on some courses)only one row
is returned per course taken. I am working to understand how the
joins and subquery work together so I can edit it.

#15

"rkc" <rkc (AT) rochester (DOT) yabba.dabba.do.rr.bomb> wrote

Quote:

David Cressey wrote:
Let's start out with a simpler problem, namely to produce just the first
two
columns of your specified result.

Maybe

SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID

The fact that we are using a pair of foreign keys as the join condition
seems odd. But then again, it's a many to many relationship. Test the
above, to see that it works.

Now, if it does work, use it as a subquery

SELECT er.EmpID, er.CourseID, c.DateTaken
FROM
(SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID) er,
LEFT JOIN CompletedCourses ON er.EmpID = c.EmpID AND er.CourseID =
c.CourseID

Note that I never used the Positions table! It's still a good idea to
have
that table for the sake of referential integrity.

Thanks to both for the continuing effort on this.

This query runs in an instant and so close to the result I am
after that it hurts.

What is happening now is that when an employee has taken a course
more than once (certification expires on some courses)only one row
is returned per course taken. I am working to understand how the
joins and subquery work together so I can edit it.

That is surprising to me. It sounds like the LEFT JOIN is doing something
different than what I expect.

What does the query look like after you fixed the errors in my earlier
response?

#16

David Cressey wrote:

Quote:

"rkc" <rkc (AT) rochester (DOT) yabba.dabba.do.rr.bomb> wrote in message
news:gq8Ag.15037$8j3.7765 (AT) twister (DOT) nyroc.rr.com...
David Cressey wrote:
Let's start out with a simpler problem, namely to produce just the first
two
columns of your specified result.

Maybe

SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID

The fact that we are using a pair of foreign keys as the join condition
seems odd. But then again, it's a many to many relationship. Test the
above, to see that it works.

Now, if it does work, use it as a subquery

SELECT er.EmpID, er.CourseID, c.DateTaken
FROM
(SELECT EmpID, CourseID
FROM Employees e,
RequiredCourses r,
WHERE
e.PosID = r.PosID) er,
LEFT JOIN CompletedCourses ON er.EmpID = c.EmpID AND er.CourseID =
c.CourseID

Note that I never used the Positions table! It's still a good idea to
have
that table for the sake of referential integrity.

Thanks to both for the continuing effort on this.

This query runs in an instant and so close to the result I am
after that it hurts.

What is happening now is that when an employee has taken a course
more than once (certification expires on some courses)only one row
is returned per course taken. I am working to understand how the
joins and subquery work together so I can edit it.

That is surprising to me. It sounds like the LEFT JOIN is doing something
different than what I expect.

What does the query look like after you fixed the errors in my earlier
response?

The primary key on the completedcourses table should be comprised of
all three fields within that table. Other than that, the queries should
work perfectly.

#17

David Cressey wrote:

Quote:

What does the query look like after you fixed the errors in my earlier
response?

The following is the query I am running now.
The goofy variations in the column names are
not my doing.

SELECT
er.[Emp-ID],
er.[Course-ID],
c.[Completed On]
FROM
(SELECT
[Emp-ID],
[Course-ID]
FROM
Employees e,
RequiredCourses r
WHERE
e.[Pos-ID] = r.[Pos-ID]) AS er
LEFT JOIN CompletedCourses AS c
ON (er.[Course-ID] = c.[Course ID])
AND (er.[Emp-ID] = c.[Emp ID])
ORDER BY [Emp-id], [Course-ID];

The more I look at the result returned by
this query the more I think that it is
working exactly as desired. Taking a single
test more than once seems to be showing up
after all. I'll have to run a few more tests
to be sure, but it's looking good.

Thanks once again to both of you for your
help with this.

#18

"rkc" <rkc (AT) rochester (DOT) yabba.dabba.do.rr.bomb> wrote

Quote:

David Cressey wrote:

What does the query look like after you fixed the errors in my earlier
response?

The following is the query I am running now.
The goofy variations in the column names are
not my doing.

SELECT
er.[Emp-ID],
er.[Course-ID],
c.[Completed On]
FROM
(SELECT
[Emp-ID],
[Course-ID]
FROM
Employees e,
RequiredCourses r
WHERE
e.[Pos-ID] = r.[Pos-ID]) AS er
LEFT JOIN CompletedCourses AS c
ON (er.[Course-ID] = c.[Course ID])
AND (er.[Emp-ID] = c.[Emp ID])
ORDER BY [Emp-id], [Course-ID];

The more I look at the result returned by
this query the more I think that it is
working exactly as desired. Taking a single
test more than once seems to be showing up
after all. I'll have to run a few more tests
to be sure, but it's looking good.

Thanks once again to both of you for your
help with this.

You're welcome. A few things that can be gleaned from this experience.

First, the subquery maybe should have had the keyword DISTINCT in it. But
the chances are the constraints on the data prevent duplicate rows on the
subquery anyway. Would adding the keyword DISTINCT make the query run
faster or slower? This is left as an exercise to the reader.

Second, and more important, is that you need to come up with a query that is
logically correct before you shift your focus onto speed. The simpler you
can keep the logically correct solution, the easier it's going to be to
tweak it so that it runs fast.
The query I presented may not look simple, but it is, once you learn to
view joins on relational tables as primitive operations.

(For the benefit of picky lurkers, yeah, yeah, I know that a relational
table and an SQL table are different. But thinking in relational terms
still helps you code in SQL).

Third, and most important, there is an enormous body of transformations that
can be accomplished using join, project, and restrict together with set
operations and ordinary arithmetic operations.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL to combine required and completed training courses.

comp.databases comp.databases

Re: SQL to combine required and completed training courses. - 07-27-2006 , 03:25 AM

Re: SQL to combine required and completed training courses. - 07-27-2006 , 04:56 AM

Re: SQL to combine required and completed training courses. - 07-29-2006 , 03:01 PM

Re: SQL to combine required and completed training courses. - 08-02-2006 , 04:03 PM

Re: SQL to combine required and completed training courses. - 08-03-2006 , 02:33 PM

Re: SQL to combine required and completed training courses. - 08-03-2006 , 05:41 PM

Re: SQL to combine required and completed training courses. - 08-06-2006 , 08:43 PM

Re: SQL to combine required and completed training courses. - 08-07-2006 , 06:36 AM