union query question (maybe!) 1

[danwand]

Hi,

I have 2 tables with the following fields:

tblStaffDetails tblTrainingMeetings

StaffID (primary key) StaffID (primary key)
FName TrainID (primary key)
MName
LName
Address
etc...

I need a query that shows all staff members from tblStaffDetails who do do yet have a record in tblTrainMeetings.

Any help would be appreciated
thanks

Dan

 

[misscrf]

who do have a record?

first, the trainingmeetings should be the staffid as a foreign key not primary.

Then - just do a query, add both tables in the design, connect the staffid and take a look. You can make it count or whatever.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[HitechUser]

danwand,

Something like this.. maybe..

SELECT * FROM tblStaffDetails WHERE StaffID NOT IN (select StaffID from tblTrainingMeetings );

Hope this helps...

 

[misscrf]

ahhh.

I didn't realize you wanted not in. Hitech has a good suggestion. If you want 2 full suggestions. you can do mine too and then just make it a group by, and make .tbltrainingmeetings.staffid a where and put is null in the criteria of the staffid. of the tbltrainingmeetings

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

SELECT D.*
FROM tblStaffDetails D LEFT JOIN tblTrainingMeetings M On D.StaffID = M.StaffID
WHERE M.StaffID Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[danwand]

thanks hitechUser that worked fine

much appreciated

Dan

 

[misscrf]

Thank HitechUser
for this valuable post!


star for you hitech!

misscrf

It is never too late to become what you could have been ~ George Eliot