Holmes Holmes - 2 months ago 13
SQL Question

SQL Allow One Return Value for Field

In my office, it is common people who belong to two departments. For a query, I want to limit it so each person is only associated with one department.

For example, here are two HR tables:

table1

ID | Last_Name | Department
--------------------------------
1 Doe Informatics
2 Miller Database Management
2 Miller Informatics
3 Johnson Engineering
4 Mitchell Database Management
4 Mitchell Engineering


table2

ID | Date_of_sale
--------------------------------
1 8-2-2012
1 1-4-2003
2 5-23-2000
2 1-17-2003
2 12-30-2001
2 9-8-2013
3 4-19-2013
4 3-8-2015
4 11-8-2013
4 2-12-2007


I want to do something like:

SELECT Last_Name, FIRST(Department), Date_of_Sale
From column1,
FROM column1 JOIN column2 ON column1.id=column2.id
GROUP BY Last_Name


Which would return:

Last_Name | Department | Date_of_sale
----------------------------------------
Doe Informatics 8-2-2012
Doe Informatics 1-4-2003
Miller Database Management 5-23-2000
Miller Database Management 1-17-2003
Miller Database Management 12-30-2001
Miller Database Management 9-8-2013
Johnson Engineering 4-19-2013
Mitchell Database Management 3-8-2015
Mitchell Database Management 11-8-2013
Mitchell Database Management 2-12-2007


Where each individual now only has one department associated with them.

Answer

With out knowing your RDBMS giving specific answer is not as easy. A general answer that woks on most systems would be to use a nested select with aggregation:

SELECT c1.ID, c1.Last_name, c1.Department, c2.Date_of_Sale
FROM
    (SELECT
       ID
       ,Last_name
       ,MIN(Department) as Department
    FROM
       column1
    GROUP BY
       ID
       ,Last_name) c1
    INNER JOIN column2 c2
    ON c1.ID = c2.ID

Then on SQL-SERVER and most other RDBMS that support common table expressions and window functions you can create a partitioned ROW_NUMBER() to choose what you want (note the cte can also be a nested select instead.

;WITH cte AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (PARTITION BY  ID ORDER BY Department) as RowNumber
    FROM
       column1
)

SELECT
    c1.Id
    ,c1.Last_name
    ,c1.Department
    ,c2.Date_of_Sale
FROM
    cte c1
    INNER JOIN column2 c2
    ON c1.Id = c2.Id
WHERE
    c1.RowNumber = 1

That is a typical way of doing it if you will somehow put a presedence on which department you will return, e.g. Record Creation Date...But if window functions are supported and you don't really care which department you can grab the partitioned MIN() or MAX() which would just be the ascending order of the records in 1 select statement.

SELECT DISTINCT
    c1.ID
    ,c1.Last_name
    ,MIN(c1.Department) OVER (PARTITION BY c1.ID) as Department
    ,c2.Date_of_Sale
FROM
    column1 c1
    INNER JOIN column2 c2
    ON c1.ID = c2.ID