I'm trying to write a script for interbase and I've hit a wall. firstly I have a simple script which is fine.
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?