Hi Leonard,
Something along the lines of this may work. I'm sure you'll need to
tweak it a bit depending on your RDBMS and other requirements.
SELECT t.trng_code, t.trng_title, c.trng_status
FROM tbl_training_calendar t
LEFT OUTER JOIN tbl_training_completed c ON c.trng_code=t.trng_code
INNER JOIN tbl_personnel p ON p.emp_id=c.emp_id
ORDER BY t.trng_code
Pretty sure that will work, but I know that when I use short-hand join
notation it will fail because of the inner reference to an outer
joined element. Formal join notation usually works. However, that
query gives you ALL employees, and I don't think you can reliably
limit the query to the currently logged-in employee when using an
element in an OUTER reference.
You could use a subquery instead, which would allow you to specify a
unique employee:
SELECT t.trng_code, t.trng_title,
(SELECT c.trng_status FROM tbl_training_completed WHERE
c.trng_code=t.trng_code AND
c.emp_id=[currently_logged_in_employee_id]) as training_status
FROM FROM tbl_training_calendar t
ORDER BY t.trng_code
But here's a question: if they are only holding training that have
been completed in the tbl_training_completed table, why the
trng_status column? Maybe the assumption that the table only holds
completed trainings is not correct? That would certainly simplify
things a bit.
Cheers,
Kris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Download the latest ColdFusion 8 utilities including Report Builder,
plug-ins for Eclipse and Dreamweaver updates.
http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291117
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
