-
Join without multiple rows ...
I have a query which joins two tables; an employee table and a site tables. Each employee record is unique with an employee name, and employee number.
I have a query that pulls employee data from the employee table and needs to match at least (and only) one site per employee, however, since one employee can be associated with more than 1 site, the query returns 1 row for each employee for each site with which he is associated.
For example, the results for my own record record in the employee look as follows:
Jeff Robinson 123xxx Phoenix
Jeff Robinson 123xxx Dallas
Jeff Robinson 123xxx Denver
Jeff Robinson 123xxx San Andreas
The query:
Select
employeeName
employeeNumber
siteName
From employeeTable
JOIN siteTable ON empNumber = empNumberFK
I want the query to return only one row per employee and I don't care which site, but it has to include at the site name.
I'm sure the solution is fairly simple, but so far, it's eluding me and any help on this will be much appreciated.
Thanks.
-
-
Jeff, not caring which site doesn't really make any logical sense but you can do it with the MIN or MAX function.
Code:
SELECT EMPLOYEENAME
, EMPLOYEENUMBER
, MIN(SITENAME)
FROM EMPLOYEETABLE
INNER JOIN
SITETABLE
ON EMPLOYEENUMBER = EMPNUMBERFK
GROUP BY EMPLOYEENAME
, EMPLOYEENUMBER
Note: MIN will get you the first Site alphabetically and Max will get you the last one.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|