Stephen Lasky Stephen Lasky - 6 months ago 8
SQL Question

SQL Join Tables Apply Value Only Once To Each Set

I need to join two tables together. However, the table being joined must only apply its value once to a set a rows with the same value. This is what I mean...

TABLE JOIN I WANT IS BELOW


** LOGGED HOURS ** ** SICK HOURS ** ** RESULT TABLE **
+--------+-------+ +--------+-------+ +--------+-------+-------+
|Name | Hours | |Name | Hours | |Name |Hours |Sick |
+--------+-------+ +--------+-------+ +--------+-------+-------+
|David |47 | |David |9 | |David |47 |9 |
+--------+-------+ +--------+-------+ +--------+-------+-------+
|David |9 | |David |9 |0 |
+--------+-------+ +--------+-------+-------+


NORMAL LEFT TABLE JOIN RESULT:


** LOGGED HOURS ** ** SICK HOURS ** ** RESULT TABLE **
+--------+-------+ +--------+-------+ +--------+-------+-------+
|Name | Hours | |Name | Hours | |Name |Hours |Sick |
+--------+-------+ +--------+-------+ +--------+-------+-------+
|David |47 | |David |9 | |David |47 |9 |
+--------+-------+ +--------+-------+ +--------+-------+-------+
|David |9 | |David |9 |9 |
+--------+-------+ +--------+-------+-------+


Notice, 9 is applied to EACH row in a normal left table join. I want 9 to be applied ONLY ONCE to the set of rows whose name is DAVID.

Feel free to as any questions, thanks.

EDIT: If you're going to down-vote my question, please provide the courtesy of explaining why and how I can improve my question in the future. Thank you.

Answer

Since you just want a single record for each "Name" to get your sick hours and the selection can be arbitrary, you can use a Window Function:

SELECT
    LoggedHours.Name,
    LoggedHours.Hours,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY LoggedHours.Name ORDER BY 1) = 1 THEN SickHours.Hours ELSE NULL END AS SickHoursLogged
FROM 
    LoggedHours LEFT OUTER JOIN SickHours on LoggedHours.Name = SickHours.Name

So... that Window/Analytics function says:

Split the result set by Name (group of records with "David" for example) and order them by whatever. Then give that group of records row numbers starting at 1. If this particular record is row 1, then it gets the sick hours from the SickHours table. Otherwise it gets Null.

If... you wanted it to not be arbitrary, but instead wanted the record with the largest logged hours for each person to get the sick hours, you could change that ROW_NUMBER() function to:

ROW_NUMBER() OVER (PARTITION BY LoggedHours.Name ORDER BY LoggedHours.Hours DESC) = 1 
Comments