Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


Elroacho

1 post

Wannabe Geek


#1600 24-May-2004 23:24
Send private message

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?

Create new topic

Kyo

Kyo
4 posts

Wannabe Geek


  #6081 27-May-2004 15:52
Send private message

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


  #6109 28-May-2004 07:25
Send private message

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

Create new topic




News »

Logitech introduce MX Anywhere 3
Posted 21-Sep-2020 21:17


Countdown unveils contactless shopping with new Scan&Go tech
Posted 21-Sep-2020 09:48


HP unveils new innovations for businesses adapting to rapidly evolving workstyles and workforces
Posted 17-Sep-2020 15:36


GoPro launches new HERO9 Black camera
Posted 17-Sep-2020 09:45


Telecommunications industry launches new 5G Facts website
Posted 17-Sep-2020 07:56


New Zealand ranks 3rd in world in GSMA index
Posted 15-Sep-2020 10:13


Trend Micro Security Suite adds web monitoring to prevent identity theft
Posted 14-Sep-2020 15:37


NVIDIA to acquire Arm for US$ 40 billion
Posted 14-Sep-2020 12:27


Epson launches its next gen A3+ colour EcoTank multi-function printer
Posted 10-Sep-2020 16:08


Sony launches three new native 4K SXRD home cinema projectors
Posted 9-Sep-2020 18:00


Catalyst Cloud brings Kubernetes-based open-source web hosting solution to market
Posted 9-Sep-2020 17:54


Verizon Connect eyes further growth in New Zealand
Posted 8-Sep-2020 09:26


PNY launches XLR8 gaming NVIDIA GeForce RTX 30 series powered by the all-new NVIDIA Ampere architecture
Posted 3-Sep-2020 16:39


NVIDIA delivers greatest-ever generational leap with GeForce RTX 30 Series GPUs
Posted 3-Sep-2020 16:17


Weta Digital advances visual effects and animation in the cloud with AWS
Posted 2-Sep-2020 17:09



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.