2

I have a query I am trying to return all users from with their join_date based on a LEFT JOIN. The query is like so:

SELECT users.name,
MIN(history.time_out) as join_date
FROM flexlm_user users
LEFT JOIN flexlm_history history ON users.id=history.user_id
GROUP BY users.name
ORDER BY 2

However, the results are returning Null for join_date where history.time_in is Null. The history table has no Nulls for the time_out field. Any help is much appreciated.

current results are like this

name    join_date
user1   NULL
user2   NULL
user3   NULL
user4   48:00.0
user5   31:00.0
user6   59:00.0
user7   15:00.0

I want results like this:

name    join_date
user1   13:00.0
user2   5:00.0
user3   7:00.0
user4   4:00.0
user5   31:00.0
user6   59:00.0
user7   15:00.0

History Table:

CREATE TABLE [dbo].[flexlm_history](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [server_id] [int] NOT NULL,
    [user_id] [int] NOT NULL,
    [product_id] [int] NOT NULL,
    [update_id] [int] NOT NULL,
    [time_out] [datetime] NOT NULL,
    [time_in] [datetime] NULL
)

Sample Data:

+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+
| id  | server_id | user_id | product_id | update_id |        time_out         |         time_in         |
+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+
| 615 |         2 |       9 |          8 |      4670 | 2017-07-28 14:10:00.000 | 2017-07-28 14:43:00.000 |
| 616 |         2 |       9 |         10 |      4670 | 2017-07-28 14:10:00.000 | 2017-07-28 14:43:00.000 |
| 617 |         2 |      24 |          9 |      4672 | 2017-07-28 14:13:00.000 | NULL                    |
| 618 |         2 |       2 |          9 |      4672 | 2017-07-28 14:14:00.000 | NULL                    |
| 619 |         2 |      76 |          4 |      4676 | 2017-07-28 14:42:00.000 | 2017-07-28 16:53:00.000 |
| 620 |         3 |      18 |         20 |      4685 | 2017-07-28 15:22:00.000 | 2017-07-28 15:53:00.000 |
| 621 |         2 |      21 |          4 |      4686 | 2017-07-28 15:29:00.000 | 2017-07-28 16:33:00.000 |
| 622 |         2 |      24 |          4 |      4690 | 2017-07-28 15:48:00.000 | 2017-07-28 16:33:00.000 |
| 623 |         2 |       9 |          2 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 624 |         2 |       9 |          8 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 625 |         2 |       9 |         10 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 626 |         2 |      43 |          2 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 627 |         2 |      43 |          5 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 628 |         2 |      43 |          6 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 629 |         2 |      43 |          8 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 630 |         2 |      43 |          9 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 631 |         2 |      43 |         10 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 632 |         2 |      43 |         11 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+

User Table:

CREATE TABLE [dbo].[flexlm_user](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](25) NOT NULL,
)

Sample Data:

+----+-------+
| id | name  |
+----+-------+
|  9 | user1 |
| 24 | user2 |
| 76 | user3 |
|  2 | user4 |
| 18 | user5 |
| 21 | user6 |
| 24 | user7 |
|  9 | user8 |
| 43 | user9 |
+----+-------+
  • @ians; you've got a major mismatch in your sample data and the current/desired result sets, eg, none of the desired join_date values show up in the sample data's time_out column; please (re)review your data points and make sure they are accurate – markp-fuso Jul 29 '17 at 22:45
  • This is working as it should. Take a look at dbfiddle here. BTW, agree with @markp comment. – joanolo Jul 29 '17 at 22:57
  • 3
    @ians: it doesn't contain nulls, but the row corresponding to the user_id you're looking for simple isn't there, and you're using a LEFT JOIN. The LEFT JOIN produces NULL for all columns of the right table if there isn't any match in the join condition. – joanolo Jul 29 '17 at 23:03
  • 1
    @joanolo you are correct I am somehow missing the related user_id in the history table. Not sure how i missed that. Thanks. – its30 Jul 29 '17 at 23:16
1

how about going the OUTER APPLY route. You can modify the inner query to handle nulls differently, i've just disregarded them but if you want to handle them you could use ISNULL or CASE in the future.

SELECT users.name,
       H.join_date
FROM flexlm_user users
    OUTER APPLY
(
    SELECT MIN(history.time_out) AS join_date
    FROM flexlm_history history
    WHERE history.user_id = users.id
          AND history.time_out IS NOT NULL
) H
ORDER BY H.join_date;
|improve this answer|||||
  • While outer apply will return the desired results, it will incur a performance penalty when searching for more than one user at a time. The Apply joins are all row by row executions. – Jonathan Fite Jul 31 '17 at 12:23
  • @JonathanFite technically yes but practically on smaller results sets you may not see a difference in processing time. I generated a mock up of the situation with 1Million users and 10 Million History entries, and ran our two suggestions both complete in 6 seconds, in statistics there's less that 100 milliseconds difference between them. Its true outer apply will hit the disk a lot more but if you have decent hardware it negates any performance cost negatives. – Ollie Jul 31 '17 at 14:53
1

This query should work for your needs. It will show you all users and their join_date (if they have one).

SELECT users.name
    , History.join_date
FROM flexlm_user AS users
    LEFT OUTER JOIN (
                    SELECT H.user_id, MIN(H.time_out) AS join_date
                    FROM flexlm_history AS H
                    GROUP BY H.user_id
                    ) AS History ON History.user_id = users.id
ORDER BY History.join_date;

If you need to only show users with a join_date, then change it to an INNER JOIN instead.

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.