anglandp
asked on
SQL - Help with a Query
I have an employees table (PS_EMPLOYEES) from which I'm pulling the following
- employeeID (primary key)
- name
- user name
- title
I have a job table (PS_JOBS) from which I'm pulling the following
- employeeID (primary key)
- FileNumber
When the results come back, some employees are listed twice, because in the jobs table they have more then one file number. I need the record which has the larger file number. How would I adjust the query below to do that? Thanks!
SELECT distinct PS_EMPLOYEES.EMPLID, PS_EMPLOYEES.NAME, PS_EMPLOYEES.FIRST_NAME, PS_EMPLOYEES.MIDDLE_NAME, PS_EMPLOYEES.LAST_NAME,
PS_EMPLOYEES.BUSINESS_TITL E, PS_JOB.FILE_NBR
FROM PS_EMPLOYEES LEFT OUTER JOIN
PS_JOB ON PS_EMPLOYEES.EMPLID = PS_JOB.EMPLID
- employeeID (primary key)
- name
- user name
- title
I have a job table (PS_JOBS) from which I'm pulling the following
- employeeID (primary key)
- FileNumber
When the results come back, some employees are listed twice, because in the jobs table they have more then one file number. I need the record which has the larger file number. How would I adjust the query below to do that? Thanks!
SELECT distinct PS_EMPLOYEES.EMPLID, PS_EMPLOYEES.NAME, PS_EMPLOYEES.FIRST_NAME, PS_EMPLOYEES.MIDDLE_NAME, PS_EMPLOYEES.LAST_NAME,
PS_EMPLOYEES.BUSINESS_TITL
FROM PS_EMPLOYEES LEFT OUTER JOIN
PS_JOB ON PS_EMPLOYEES.EMPLID = PS_JOB.EMPLID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this did it perfectly. Thank you kindly!
Open in new window