finance_teacher
asked on
Oracle -- LEFT Join "Linking" ?
Below fails when I uncomment the TRUNC line.
How can I setup this LEFT JOIN so it works
and makes b.amount "0" if there are no
associated EMPLOYEE_SALARY_TAB records ?
SELECT a.emp_no,
SUM(a.hours) Total_Hours,
SUM(a.hours)*b.amount Total_Dollars,
MAX(a.foreign_trn_id) foreign_trn_id
FROM EXT_WAGE_TRANSACTION_TAB A
LEFT JOIN EMPLOYEE_SALARY_TAB B
ON A.EMP_NO = B.EMP_NO
WHERE
--TRUNC (a.from_time) >= TRUNC(b.valid_from) AND TRUNC (a.from_time) <= TRUNC(b.valid_to) and
a.emp_no = '465521'
GROUP BY a.emp_no,
B.AMOUNT
ORDER BY a.EMP_NO
How can I setup this LEFT JOIN so it works
and makes b.amount "0" if there are no
associated EMPLOYEE_SALARY_TAB records ?
SELECT a.emp_no,
SUM(a.hours) Total_Hours,
SUM(a.hours)*b.amount Total_Dollars,
MAX(a.foreign_trn_id) foreign_trn_id
FROM EXT_WAGE_TRANSACTION_TAB A
LEFT JOIN EMPLOYEE_SALARY_TAB B
ON A.EMP_NO = B.EMP_NO
WHERE
--TRUNC (a.from_time) >= TRUNC(b.valid_from) AND TRUNC (a.from_time) <= TRUNC(b.valid_to) and
a.emp_no = '465521'
GROUP BY a.emp_no,
B.AMOUNT
ORDER BY a.EMP_NO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will test
angelIII:
If there is no records in EMPLOYEE_SALARY_TAB then the below condition also will fail so whats the purpose of adding those condition in ON clause
AND TRUNC (a.from_time) >= TRUNC(b.valid_from)
AND TRUNC (a.from_time) <= TRUNC(b.valid_to)
as sdstuber: said nvl(amount,0), is this not enough and is we need to add those condition in ON clause?
If there is no records in EMPLOYEE_SALARY_TAB then the below condition also will fail so whats the purpose of adding those condition in ON clause
AND TRUNC (a.from_time) >= TRUNC(b.valid_from)
AND TRUNC (a.from_time) <= TRUNC(b.valid_to)
as sdstuber: said nvl(amount,0), is this not enough and is we need to add those condition in ON clause?
angelIII:
If there is no records in EMPLOYEE_SALARY_TAB then the below condition also will fail so whats the purpose of adding those condition in ON clause
AND TRUNC (a.from_time) >= TRUNC(b.valid_from)
AND TRUNC (a.from_time) <= TRUNC(b.valid_to)
the conditions for the (LEFT) JOIN, are evaluated for the JOIN only, so if you have:
SELECT ...
FROM A
LEFT JOIN B on <complex condition>
it will return all records from table A, even if there is no match on <complex condition> for any rows in table B
consider:
SELECT *
FROM A
LEFT JOIN B on 1=0
the condition will be always false. still all records for table A will be returned ... with all columns from table B being returned NULL.
IF you put that condition into WHERE, it's different, because WHERE conditions are evaluated AFTER all join conditions, aka on the "intermediate results"
consider:
SELECT *
FROM A
LEFT JOIN B on A.ID = B.ID
WHERE 1 = 0
here, the WHERE clause will make no records will ever be returned for this query, whatever you have in table A or B
I hope this clarifies
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window