Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

Possible Join Query Prob.

Sort:
You are not authorized to post a reply.
Author
Messages
DavidBrianCampbell
Basic Member
Posts: 13
Basic Member
9/19/2007 12:41 PM
    Thanks in advance... I have this query below, which was working fine till I added "DEPTCODE.DEPARTMENT, DEPTCODE.NAME" and included "DEPTCODE" in the FROM clause... this was to pick up the department name associated with the employee... I'm guessing I need an additional JOIN clause (nested?)

    SELECT EMPLOYEE.EMPLOYEE, EMPLOYEE.LAST_NAME, EMPLOYEE.DEPARTMENT, EMPLOYEE.POSITION, EMPLOYEE.FIRST_NAME, DEPTCODE.DEPARTMENT, DEPTCODE.NAME, PAYDEDUCTN.EMPLOYEE, PAYDEDUCTN.CHECK_ID, PAYDEDUCTN.WAGE_AMOUNT, PAYDEDUCTN.DED_CODE, PAYDEDUCTN.DED_AMT, PAYDEDUCTN.PER_END_DATE, trim(last_name) || ', ' || trim(first_name) || ' ' || case when trim(middle_init) is null then trim(middle_init) else trim(middle_init) || '.' end FROM PAYDEDUCTN PAYDEDUCTN INNER JOIN EMPLOYEE EMPLOYEE ON (PAYDEDUCTN.COMPANY=EMPLOYEE.COMPANY) AND (PAYDEDUCTN.EMPLOYEE=EMPLOYEE.EMPLOYEE), DEPTCODE
    Chris Martin
    Veteran Member
    Posts: 277
    Veteran Member
    9/19/2007 12:45 PM
      You need to join employee and deptcode on 1) company 2) process_level and 3) department. Inner join if all employees you want to include have a department populated on the employee table, otherwise do an outer join. No needed for any nesting here.
      You are not authorized to post a reply.