Link to home
Start Free TrialLog in
Avatar of finance_teacher
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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you need to make hte condition part of the left join;
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
  AND TRUNC (a.from_time) >= TRUNC(b.valid_from) 
   AND TRUNC (a.from_time) <= TRUNC(b.valid_to) 
  WHERE  a.emp_no = '465521'
  GROUP BY a.emp_no,
    B.AMOUNT
  ORDER BY a.EMP_NO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of finance_teacher
finance_teacher

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?
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial