Exists or not?

[Elroacho]

Hi all,

I'm trying to write a script and I've hit a wall. firstly I have a simple script which is fine.

SELECT STAFF.STAFF_ID,
STAFF.NAME,
STAFF AGE,
SALARY.WAGES
FORM STAFF, SALARY
WHERE SALARY > 10000 AND
STAFF.STAFF_ID = SALARY_STAFF_ID

OK it's easy to see what this is doing but I need to add a boolean field called COMPANY CAR. The value will come from another table called BENEFITS. The benefits table comtains the fields STAFF_ID and BENEFIT. the BENEFIT field will contain various codes such as 'CAR', 'PEN' and BONUS' so one employee could have more than one benefit code assigned to them so they will have multiple records. The COMPANY CAR field in the output will return 'CAR' (or just 'TRUE') if they have the 'CAR' code with their STAFF_ID in the BEFEFITS table or FALSE if they don't. As you can see my problem is there is no 'NO CAR' value in the BENFITS table so no record for them will exist if they don't have a car.

Can you help?

YNWA
Elraocho

 

[blom0344]

You probably will have resolved this by now, but as Interbase supports outer joins this can be resolved with an outer join between the STAFF and BENEFIT table?

This will fetch all STAFF records regardless whether there is a suitable match from the BENEFIT table.....

T. Blom
Information analyst
tbl@shimano-eu.com