WHERE ... NOT IN ( .... ) Not excluding values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

WHERE ... NOT IN ( .... ) Not excluding values

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-30 : 12:11:44

I have this statement where I am trying to exclude all the values in the IN clause, but they are right there in the results with the others. What am I doing wrong. I at first had the NOT IN clause in the JOIN part only. Then I added it to the HAVING clause, but it had no effect. All the values are still there. Otherwise the query works as intending bringing back only those records where there is more than one product for each userID. I am trying to find multiple installations of Office used by each user not wanting the Service Packs, trial editions, etc. to count. Thank you for any help.

SELECT O.UserID, O.WorkstationID, O.Product, O.[Version]
FROM dbo.OffceInstallations O
INNER JOIN 
(
SELECT UserID, Product
FROM [SoftwareReporting].[dbo].[OffceInstallations]
WHERE Product NOT IN (
'Microsoft Office 2003 SP2',
'Microsoft Office Professional 2007 Trial',
'Microsoft Office Shared 64-bit MUI (English) 2007',
'Microsoft Office Shared MUI (English) 2007',
'Microsoft Office Shared Setup Metadata MUI (English) 2007',
'Microsoft Office Small Business Connectivity Components'
)
GROUP BY UserID, Product
HAVING (COUNT(UserID) > 1) AND Product NOT IN (
'Microsoft Office 2003 SP2',
'Microsoft Office Professional 2007 Trial',
'Microsoft Office Shared 64-bit MUI (English) 2007',
'Microsoft Office Shared MUI (English) 2007',
'Microsoft Office Shared Setup Metadata MUI (English) 2007',
'Microsoft Office Small Business Connectivity Components'
)
) U
ON O.UserID = U.UserID
ORDER BY O.UserID,  O.WorkstationID, O.Product, O.Version

Duane

Kristen
Test

22859 Posts

Posted - 2010-03-30 : 12:29:28

You are joining to OffceInstallations AS U only on UserID

Presumably that user ALSO has Products in OffceInstallations AS O that are in the "NOT IN" list

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-30 : 12:56:32

Thank you for your response. Maybe you can help me a little further. I joined on Product as well and it got rid of the bad records, but I am getting duplicate rows somehow so I must be getting some kind of CROSS JOIN. As you can see in my code below, I used a DISTINCT to get rid of the duplicates, and it worked, but I know that this is not the way to handle it properly. I would appreciate if someone could help me filter out the duplicates right from the start.

SELECT DISTINCT O.UserID, O.WorkstationID, O.Product, O.[Version]
FROM dbo.OffceInstallations O
INNER JOIN 
(
SELECT UserID, Product
FROM [SoftwareReporting].[dbo].[OffceInstallations]
WHERE Product NOT IN (
'Microsoft Office 2003 SP2',
'Microsoft Office Professional 2007 Trial',
'Microsoft Office Shared 64-bit MUI (English) 2007',
'Microsoft Office Shared MUI (English) 2007',
'Microsoft Office Shared Setup Metadata MUI (English) 2007',
'Microsoft Office Small Business Connectivity Components'
)
GROUP BY UserID, Product
HAVING (COUNT(UserID) > 1) AND Product NOT IN (
'Microsoft Office 2003 SP2',
'Microsoft Office Professional 2007 Trial',
'Microsoft Office Shared 64-bit MUI (English) 2007',
'Microsoft Office Shared MUI (English) 2007',
'Microsoft Office Shared Setup Metadata MUI (English) 2007',
'Microsoft Office Small Business Connectivity Components'
)
) U
ON O.UserID = U.UserID And O.Product = U.Product
ORDER BY O.UserID, O.Product, O.Version, O.WorkstationID

Thanks again.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources