SQL syntax help

[woyler]

Hello all.
I need a hand with a query, and need to ask for your brain power. This is very similar to a post I made in the past, which I thought I had working, but I still have an issue.

I have 2 tables

Table MAIN_TABLE
F_NAME
L_NAME
MINUTES_WORKED

Table VACATION_TABLE
F_NAME
L_NAME
VACATION_MINUTES


MAIN_TABLE (Example data)
F_NAME L_NAME WEEK_NUMBER MINUTES_WORKED
Al Smith 1 2000
Ben Johnson 6 2100
Cathy Stevens 7 1500


VACATION_TABLE (Example data)
F_NAME L_NAME VACATION_MINUTES
Al Smith 50
Cathy Stevens 90
Cathy Stevens 20

I want the results to appear like so;

F_NAME L_NAME MINUTES_WORKED VACATION_MINUTES
Al Smith 2000 50
Ben Johnson 2100 0
Cathy Stevens 1500 110


Using the below query , I get the following results

SELECT MAIN_TABLE.F_NAME , MAIN_TABLE.L_NAME, sum(MAIN_TABLE.MINUTES_WORKED) as MinutesWorked
sum(tmp.VACATION_MINUTES) as VacationMinutes
FROM MAIN_TABLE
LEFT JOIN(SELECT F_NAME, L_NAME, VACATION_MINUTES from VACATION_TABLE) as tmp
ON MAIN_TABLE.F_NAME = tmp.F_NAME AND
MAIN_TABLE.L_NAME = tmp.L_NAME AND
WHERE MAIN_TABLE.WEEK_NUMBER BETWEEN 1 and 8
group by MAIN_TABLE.F_NAME , MAIN_TABLE.L_NAME

F_NAME L_NAME MINUTES_WORKED VACATION_MINUTES
Al Smith 2000 50
Ben Johnson 2100 0
Cathy Stevens 3000 110

The MINUTES_WORKED for Cathy is doubled becuse there are 2 records in the VACTION_TABLE

---------------------------------------------------------------------------------------------------

To fix this I tried to sum the VACATION_MINUTES in the tmp table. I get the correct results, but the query takes 5 times as long to run.

SELECT MAIN_TABLE.F_NAME , MAIN_TABLE.L_NAME, sum(MAIN_TABLE.MINUTES_WORKED) as MinutesWorked
sum(tmp.VACATION_MINUTES) as VacationMinutes
FROM MAIN_TABLE
LEFT JOIN(SELECT F_NAME, L_NAME, sum(VACATION_MINUTES) as VACATION_MINUTES from VACATION_TABLE) as tmp
ON MAIN_TABLE.F_NAME = tmp.F_NAME AND
MAIN_TABLE.L_NAME = tmp.L_NAME AND
WHERE MAIN_TABLE.WEEK_NUMBER BETWEEN 1 and 8
group by MAIN_TABLE.F_NAME , MAIN_TABLE.L_NAME

F_NAME L_NAME MINUTES_WORKED VACATION_MINUTES
Al Smith 2000 50
Ben Johnson 2100 0
Cathy Stevens 1500 110

I get the correct results, but the query takes 5 times as long to run. Is there another way I can do this and keep the execution time down?

 

[gmmastros]

For best performance, I would recommend....


1. Create an Identity field in the main table. Each employee would be assigned to an arbitrary number. This number wouldn't mean anything to anybody EXCEPT for the queries.

2. Add the same field to the vacation table and set up a foreign key relationship.

With this structure, you would only need to join the 2 tables on an integer field instead of 2 varchar fields. Your query will run faster. I realize this would represent a fair amount of work (because other queries will have to change), but trust me when I say that you will be glad you did this.

By the way... what I just described is part of database normalization. Do a google search on "Database Normalization" and read through the first couple of sites that google suggests.

One 'side problem' that you probably already noticed is that when an employee changes name (like what happens when women get married), you must change the name in both tables. If you only store the first and last name in the main table and join to the vacation table on an arbitrary id number, then you would only need to change the name in 1 table (making data integrity a little better).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[woyler]

George,
Thanks for your response. My question is a bit more involved then I have described. I took the basis of my issue and reduced it to the 2 tables I described in my question. The 2 tables I am actually dealing with have many more columns in each. The 4 columns that make up the PK in the main table exist in the secondary table, but they can not be part of the key as there is the possibility that the value could be null. That is why I have to join them on the fields I do. I have begun trying to add an index to the secondary table to see if that speeds things up. I want to make sure the approach I am taking is a good one, structure wise, or should I be going down a different path.

regards,
Bill

 

[gmmastros]

It is better to use integers as your primary keys because the performance is better. Adding an index to the table will probably help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom