help with joining 2 tables - multiple fields on one relate to primary 1

[NickyJay]

Hi All,

I hope someone can help me.

I have a table of clients that stores in 4 fields their jobchoices - each client has upto and including 4 areas of work. I have a vacacny table with records the job type of a vacacny recorded. I need to list all vacacnies for a clients where any one of the four job choices they have specified matches the vacancy's jobtype. Really struggling to get my head around this so any help is greatfully received!

Client table
--------------
Client_ref
PreferenceOcc1_ref
PreferenceOcc2_ref
PreferenceOcc3_ref
PreferenceOcc4_ref

Vacancy table
--------------
VacancyID
Occ_Ref

I had successful query working the listed the matching vacancies where the jobtype = the 1st job choice, but getting the other 3 out is causing issues. I need to account for nulls also where a client only has 2 or 3 of the job choices complted.

Many thanks for just looking at my rambllings!!

Nicola

 

[bborissov]

SELECT * 
 FROM Clients
INNER JOIN Vacancy 
      ON Clients.PreferenceOcc1_ref = Vacancy.Occ_Ref OR
         Clients.PreferenceOcc2_ref = Vacancy.Occ_Ref OR
         Clients.PreferenceOcc3_ref = Vacancy.Occ_Ref OR
         Clients.PreferenceOcc4_ref = Vacancy.Occ_Ref
WHERE Vacancy.Occ_Ref = ??????

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NickyJay]

Perfect, i had something almost identical from continuing my google search

Many thanks for your help - think i need to get em on a training course asap!

Nicola