Results 1 to 3 of 3

Thread: Join without multiple rows ...

  1. #1
    Join Date
    Apr 2010
    Posts
    1

    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.

  2. #2
    Join Date
    Mar 2006
    Location
    Indiana
    Posts
    5
    Select top 1 . . .

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    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
  •