SQL help required

Forums › Geekzone › SQL help required

Elroacho

1 post

Wannabe Geek
Inactive user

#1600 24-May-2004 23:24

Hi all,

I'm trying to write a script for interbase 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?

Kyo

4 posts

Wannabe Geek
Inactive user

#6081 27-May-2004 15:52

Hi,

My SQL is rusty so this may not be correct.

I think you will need to add an extra table called Benefit Type table outlining the types of Benefits available to staff. The Benefit Type table will contain a Benefit Type ID and the Benefit Description, such as CAR, PEN, BONUS, NO CAR, etc, etc, etc. The Benefit Type table will have a 1:M relationship with the already existing Benefits table. This may solve the problem of not having the NO CAR value in the Benefits table. By creating the extra table to store Benefit Types, you will not risk deleting any particular Benefit from your tables (Deletion Anomaly), i.e. there is always a Benefit called NO CAR that exists in your database.

Hope this helps

jguldy

1 post

Wannabe Geek
Inactive user

#6109 28-May-2004 07:25

You could try a left join:

SELECT STAFF.STAFF_ID,

STAFF.NAME,

STAFF AGE,

SALARY.WAGES,

BENEFITS.BENEFIT

FROM STAFF, SALARY

WHERE SALARY > 10000 AND

STAFF.STAFF_ID = SALARY.STAFF_ID

Left Join

BENEFITS

ON BENEFITS.STAFF_ID = STAFF.STAFF_ID

AND ISNULL(BENEFITS.BENEFIT,'NO CAR') = 'CAR'

I *think* this should work...

JGuldy