May 21, 2014 at 8:47 am
Hi,
I need to select data where for a column is not equal to a value and also get the NULLs. The below query is not working. I can get a list of Products which are not version 'Product8800' but not NULLs.
How can I amend this query to get the NULL values?
SELECT DISTINCT
[ComputerName],
[Type],
[Products],
[ProductVersion],
[ProductCode]
FROM [dbo].[Table]
WHERE ([ProductVersion] != (SELECT [version]
FROM [dbo].[Catalogue]
WHERE productid = 'Product8800') OR [ProductVersion] IS NULL)
May 21, 2014 at 9:09 am
Have you verified the existence of null values in the ProductVersion column?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2014 at 9:09 am
sql_ques (5/21/2014)
Hi,I need to select data where for a column is not equal to a value and also get the NULLs. The below query is not working. I can get a list of Products which are not version 'Product8800' but not NULLs.
How can I amend this query to get the NULL values?
SELECT DISTINCT
[ComputerName],
[Type],
[Products],
[ProductVersion],
[ProductCode]
FROM [dbo].[Table]
WHERE ([ProductVersion] != (SELECT [version]
FROM [dbo].[Catalogue]
WHERE productid = 'Product8800') OR [ProductVersion] IS NULL)
Looks ok to me. I assume when say "get the NULLS" you mean to return rows from Table where ProductVersion IS NULL?
This query looks a little brittle to me though. If your subquery returns more than 1 row it will break. Does productid have a unique constraint in the Catalogue table?
Without ddl and sample data here is a shot in the dark for another way to do this which keep it SARGable.
SELECT DISTINCT
[ComputerName],
[Type],
[Products],
[ProductVersion],
[ProductCode]
FROM [dbo].[Table] t
left join dbo.Catalogue c on c.[Version] = t.ProductVersion and c.productid = 'Product8800'
where t.ProductVersion IS NULL
OR c.[Version] IS NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply