Chriser Chriser - 4 years ago 80
SQL Question

SQL: extracting userIDs that performed an action

Apologies for the question title, but don't know what function could achieve what I need.

For simplicity, I have below table:

UserID | date | action
----------------------------
1 | 1/1/2015 |
2 | 1/1/2015 |
2 | 2/1/2015 |
2 | 3/1/2015 |
2 | 4/1/2015 | sale
3 | 2/1/2015 |
3 | 4/1/2015 |
4 | 5/1/2015 |
4 | 7/1/2015 | sale
5 | 3/1/2015 | sale
6 | 4/1/2015 |
7 | 9/1/2015 | sale


I would like to be able to run a query where I will exclude all UserIDs which didn't end up with any action.

Therefore I would expect to see this:

UserID | date | action
----------------------------
2 | 1/1/2015 |
2 | 2/1/2015 |
2 | 3/1/2015 |
2 | 4/1/2015 | sale
4 | 5/1/2015 |
4 | 7/1/2015 | sale
5 | 3/1/2015 | sale
7 | 9/1/2015 | sale


Is this something that can be done in SQL?

Answer Source

Assuming the blanks in the action column are NULL, you can write this way:

SELECT
    u.UserID,
    u.date,
    u.action
FROM
    (SELECT UserID FROM users WHERE action IS NOT NULL GROUP BY UserID) action_user
JOIN
    users u ON u.UserID = action_user.UserId

If it's actually a blank value, you would update the criteria in the subquery action_user to WHERE u.action != ''

If your DB Engine supports EXISTS, you could use that for better readability: http://www.techonthenet.com/sql/exists.php. Avoid IN/NOT IN for sub queries that reference many rows, as that often results in a RBAR (row by row) execution with poor performance. IN/NOT IN are typically reserved for a specific set of values as opposed to a subquery.

SQL Fiddle: http://sqlfiddle.com/#!9/884dd/3

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download