| help joining 3 tables with filtering records [message #373532] |
Sun, 22 April 2001 01:23  |
amul
Messages: 252
Registered: April 2001
Location: Chennai
|
Senior Member |
|
|
SELECT M.EMP_NO,M.INOUT_DATE,M.INOUT_FLAG,M.TRANSACTION_FLAG,
E.EMP_NO,E.FLEX_NO,
F.FLEX_NO,F.FLEX_DESCRIPTION,
EM.EMP_NO
FROM FLEX_MASTER F,
EMPLOYEE_MASTER E,
MONTHLY_EMP_ATTENDANCE M,
MONTHLY_EMP_ATTENDANCE EM
WHERE M.EMP_NO = E.EMP_NO
AND E.FLEX_NO = F.FLEX_NO
AND EM.EMP_NO = (SELECT EMP_NO FROM MONTHLY_EMP_ATTENDANCE WHERE INOUT_FLAG=3)
/
THIS QUERY DOEST WORK CAN ANY BODY HELP URGENT!!!!!!!
IS THERE ANY BETTER WAY TO WRITE QUERY JOINING 3 TABLES WITH FILTERING ????
PL.HELP
|
|
|
|
| Re: help joining 3 tables with filtering records [message #373535 is a reply to message #373532] |
Sun, 22 April 2001 19:34  |
Joseph Stiehm
Messages: 4
Registered: April 2001
|
Junior Member |
|
|
As written, this SQL would produce a cartesian product of the intermediate result joining tables F, E, & M with EM. Is this intentional? The following SQL eliminates the second alias to MONTHLY_EMP_ATTENDANCE and filters the records:
SELECT M.EMP_NO,M.INOUT_DATE,M.INOUT_FLAG,M.TRANSACTION_FLAG,
E.EMP_NO,E.FLEX_NO,
F.FLEX_NO,F.FLEX_DESCRIPTION
FROM FLEX_MASTER F,
EMPLOYEE_MASTER E,
MONTHLY_EMP_ATTENDANCE M
WHERE M.EMP_NO = E.EMP_NO
AND E.FLEX_NO = F.FLEX_NO
AND M.INOUT_FLAG = 3
Let me know if this helps.
Joseph Stiehm
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
