CODEWITHSUNDEEP
sqloracle-databasejoinleft-join
Ashley Xu
Ashley Xu

Reputation: 304

How to join to a statement with a row_number() function in SQL?

I a SQL with a row_number() function, and I would like to join on additional tables to get the fields below. How would I accomplish this?

Desired fields:

EMPLOYEE.EMPLID 
  EMPLOYEE.JOBTITLE
  NAME.FIRST_NAME
  NAME.LAST_NAME
  LOCATION.ADDRESS
  PROFESSIONAL_NAME.PROF_NAME

Beginning SQL:

SELECT COUNT(*) 
FROM 
    (
        SELECT EMPLOYEE.*, ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY 
        PRIM_ROLE_IND DESC, EMPL_RCD ASC) as RN
        FROM EMPLOYEE
        WHERE JOB_INDICATOR = 'P'
    ) dt
WHERE RN = 1

When I try to add a left join at the end, I get an error that says "EMPLOYEE"."EMLID" invalid identifier.

What I'm trying:

SELECT 
  EMPLOYEE.EMPLID, 
  EMPLOYEE.JOBTITLE,
  NAME.FIRST_NAME, 
  NAME.LAST_NAME, 
  LOCATION.ADDRESS,
  PROFESSIONAL_NAME.PROF_NAME 
FROM 
    (
        SELECT EMPLOYEE.*, ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY 
        PRIM_ROLE_IND DESC, EMPL_RCD ASC) as RN
        FROM EMPLOYEE
        WHERE JOB_INDICATOR = 'P'
    ) 
    
    LEFT JOIN NAME ON EMPLOYEE.EMPLID = NAME.EMPLID 
WHERE 
  RN = 1
  AND
  NAME.EFFDT = (
    SELECT 
      MAX (NAME2.EFFDT) 
    FROM 
      NAME NAME2 
    WHERE 
      NAME2.EMPLID = NAME.EMPLID 
      AND NAME.NAME_TYPE = 'PRI'
  )  
  AND EMPLOYEE.JOB_INDICATOR = 'P'

Upvotes: 1

Views: 107

Answers (1)

EoinS
EoinS

Reputation: 5482

You just need to alias your table

...
(
    SELECT EMPLOYEE.*, ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY 
    PRIM_ROLE_IND DESC, EMPL_RCD ASC) as RN
    FROM EMPLOYEE
    WHERE JOB_INDICATOR = 'P'
) temp_employee --add this
LEFT JOIN NAME ON temp_employee.EMPLID = NAME.EMPLID 
...

When you create your new table with row_number() in an inner select you essentially create a new table. You need to alias or name this table and then refer to that alias. In the above your from is the inner select, not the EMPLOYEE table. See below for simplified example.

select newtable.field from (select field from mytable) newtable

Upvotes: 1

Related Questions