Re: SQL to combine required and completed training courses.


"rkc" <rkc@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:aVwBg.20274$8j3.17967@xxxxxxxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)