Query Help - Is this Possible?

[Brian Sheridan]

I will try to make this as simple as possible. Any help would be greatly
appreciated. I have 3 tables like below.  
   
tblEmployees
-----------------------------------------
EID | Name |
1, John
2, Bob
3, Steve
4, Brian
5, Joel
6, Lance
-----------------------------------------

tblTraining
-----------------------------------------
EID | JOB |
3, 1
4, 1
5, 3
3, 3
4, 2
-----------------------------------------

tblJobFunctions
-----------------------------------------
JID | Job_Name |
1, Picking
2, Packing
3, Shipping
-----------------------------------------

I was looking to produce results like this. Basically it will list ALL employees, and provide me with a scorecard type of training conducted. IS THIS POSSIBLE?

NAME, PICKING, PACKING, SHIPPING
John,No, No, No
Bob,No, No, No
Steve,Yes, No, Yes
Brian,Yes, Yes, No
Joel,No, No, Yes
Lance,No, No, No



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3084
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Peter Boughton]

I assume you're asking if it's possible with a single query... the
answer is maybe, but I wouldn't even bother working that out - it is
very likely going to be clearer to not do it in a single query, but to
split it out into more readable parts.

For example, select from employees and loop through that, then for
each one do a lookup for each job match and display yes/no if found or
not.

Make sense?



On Thu, Jun 5, 2008 at 5:57 AM, Brian Sheridan
<brian-sheridan@...> wrote:

> I will try to make this as simple as possible. Any help would be greatly
> appreciated. I have 3 tables like below.
>
> tblEmployees
> -----------------------------------------
> EID | Name |
> 1, John
> 2, Bob
> 3, Steve
> 4, Brian
> 5, Joel
> 6, Lance
> -----------------------------------------
>
> tblTraining
> -----------------------------------------
> EID | JOB |
> 3, 1
> 4, 1
> 5, 3
> 3, 3
> 4, 2
> -----------------------------------------
>
> tblJobFunctions
> -----------------------------------------
> JID | Job_Name |
> 1, Picking
> 2, Packing
> 3, Shipping
> -----------------------------------------
>
> I was looking to produce results like this. Basically it will list ALL employees, and provide me with a scorecard type of training conducted. IS THIS POSSIBLE?
>
> NAME, PICKING, PACKING, SHIPPING
> John,No, No, No
> Bob,No, No, No
> Steve,Yes, No, Yes
> Brian,Yes, Yes, No
> Joel,No, No, Yes
> Lance,No, No, No
>
>
>

--
 \ \
Peter Boughton
blog.bpsite.net
 / /

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3086
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6