|
|
Posted by das on 05/04/06 18:12
Hello all,
Can someone help me with this SQL?
1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and other
columns
2) EMPLOYEE_BENEFITS table has a column called employee_entity, this
column can be joined to either 'employee_id' OR 'emp_sid' but not both
in the EMPLOYEE table.
3) EMPLOYEE_TRACK table has column called employee_track_entity, this
column can be joined to the employee_benefits_id (PK) of the
EMPLOYEE_BENEFITS table.
I am listing the sql for the tables (the tables shows only the columns
in question)
CREATE TABLE [dbo].[EMPLOYEE] (
[employee_id] [int] IDENTITY (1, 1) NOT NULL ,
[empsid_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[EMPLOYEE_BENEFITS] (
[employee_benefits_id] [int] IDENTITY (1, 1) NOT NULL ,
[employee_entity] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[EMPLOYEE_TRACK ] (
[employee_track_id] [int] IDENTITY (1, 1) NOT NULL ,
[employee_track_entity] [int] NOT NULL
) ON [PRIMARY]
GO
SELECT * FROM EMPLOYEE e
INNER JOIN
EMPLOYEE_BENEFITS eb ON (e.employee_id = eb.employee_entity OR
e.empsid_id = eb.employee_entity)
INNER JOIN
EMPLOYEE_TRACK et ON eb.employee_benefits_id = et.employee_track_entity
The above SQL I wrote is this: the second inner join uses a OR to join
either of the columns in the first table EMPLOYEE. There is performance
degradation with this SQL. With huge data It takes about 30 seconds to
execute. I know this is not the perfect way to do it, can anyone of the
SQL Gurus please enlighten me to a faster approach?
If I dont use the OR I can try left join on the same table
EMPLOYEE_BENEFITS twice by changing the join types, but If I did this
what table alias can I use to join to the 3rd table?
SELECT * FROM EMPLOYEE e
LEFT JOIN
EMPLOYEE_BENEFITS eb1 ON e.employee_id = eb.employee_entity
LEFT JOIN
EMPLOYEE_BENEFITS eb2 ON e.empsid_id = eb.employee_entity
INNER JOIN
EMPLOYEE_TRACK et ON [???].employee_benefits_id =
et.employee_track_entity
thanks
adi
[Sorry I am posting this twice, on SQL Programming forum too]
Navigation:
[Reply to this message]
|