Exclude Secondary ID Records from Original SELECT

Question

I'm relatively new to SQL and am running into a lot of issues trying to figure this one out. I've tried using a LEFT JOIN, and dabbled in using functions to get this to work but to no avail.

For every UserID, if there is a NULL value, I need to remove all records of the Product ID for that UserID from my SELECT.

I am using SQL Server 2014.

Example Table

+--------------+-------------+---------------+
| UserID       | ProductID   | DateTermed    |
+--------------+-------------+---------------+
|     578      |           2 |    1/7/2017   |
|     578      |           2 |    1/7/2017   |
|     578      |           1 |    1/15/2017  |
|     578      |           1 |     NULL      |
|     649      |           1 |    1/9/2017   |
|     649      |           2 |    1/11/2017  |
+--------------+-------------+---------------+

Desired Output

+--------------+-------------+---------------+
| UserID       | ProductID   | DateTermed    |
+--------------+-------------+---------------+
|     578      |           2 |    1/7/2017   |
|     578      |           2 |    1/7/2017   |
|     649      |           1 |    1/9/2017   |
|     649      |           2 |    1/11/2017  |
+--------------+-------------+---------------+

Possible duplicate of [MySQL SELECT only not null values](http://stackoverflow.com/questions/5285448/mysql-select-only-not-null-values) — Eoghan, Jan 31 '17 at 02:39

M84 · Answer 1 · 2017-01-31T03:02:10.360

You can use this WHERE condition:

SELECT 
      UserID,ProducID,DateTermed
FROM
[YourTableName]
WHERE
        (CONVERT(VARCHAR,UserId)+ 
         CONVERT(VARCHAR,ProductID) NOT IN (    
                            select CONVERT(VARCHAR,UserId)+ CONVERT(VARCHAR,ProductID) 
                            from 
                                [YourTableName]
                            where DateTermed is null)
        )

When you concatenate the UserId and the ProductId get a unique value for each pair, then you can use them as a "key" to exclude the "pairs" that have the null value in the DateTermed field.

Hope this help.

But wouldn't that only exclude that one record, and not all of the records for that ProductID? — , Jan 30 '17 at 20:32

mAniC · Accepted Answer · 2017-05-15T20:17:08.397

Try the following:

SELECT a.userid, a.productid, a.datetermed 
FROM yourtable a
LEFT OUTER JOIN (SELECT userid, productid, datetermed FROM yourtable WHERE 
datetermed is null) b
on a.userid = b.userid and a.productid = b.productid
WHERE b.userid is not null

This will left outer join all records with a null date to their corresponding UserID and ProductID records. If you only take records that don't have an associated UserID and ProductID in the joined table, you should only be left with records that don't have a null date.

Exclude Secondary ID Records from Original SELECT

2 Answers2