Query - General - Different fields - same lookup table

Apologies if the thread title is a little misleading.

I have a lookup table called CODES that (not suprisingly!!) contains a list of codes:

CODES:
code_type
code_id
code_value

And I have a table which contains details of staff members:

STAFF:
staff_id
staff_name
employment_type
pay_type
etc...

Within the staff table certain fields use the codes table as a lookup table, for example the employment_type field can be either P, T, C or A - permanent, temporary, casual or agency - and the pay_type field can be D, M, W - daily, monthly or weekly. My problem is that all of the codes are stored in the codes table eg:
code_type code_id code_value
emp_type P permanent
emp_type T temporary
emp_type C casual
emp_type M maternity leave
emp_type A agency temp
pay_type D daily
pay_type M monthly
pay_type W weekly

As you can see, there are two records with a code_id of M, one relating to an employment type of maternity leave cover, and one with a pay_type of monthly. I am trying to write a query which retrieves the correct code_value from the codes table. This seems simple enough if I only need one of the values in a query, eg.

I also need to show the pay_type in this query, which causes a problem because I would theoretically use the same lookup table. Could anybody tell me if there is an easy way to achieve this? I know that I could just use a select...case statement in the query, however I dont want to have to change the sql every time a new employment_type is added to the database!!!

I hope this makes sense, any advice greatly appreciated.

Thanks for your time.

couldn't you try subselects. something like this

Mehere, thank you very much indeed for the reply. I have tried your suggestion but I get an error saying that the "single row subquery returns more than one row". I guess this is because there is no criteria in the subselect to narrow down the query and only return one row.

Thanks for the idea though, I will keep experimenting with this concept and see if I can get anywhere.

Finally cracked it, thought I'd post my solution in case anyone else has a similar problem.

This may or may not be the best way of going about it, but the way I solved the problem was to refer to the same CODES table several times but give it a different alias each time. In my example I was trying to retrieve the correct code_value from the codes table for two different columns:

Thanks for your time.