January 28, 2016 at 10:50 am
First I am a totoal noob as SQL and my company just thew me in the deep water and told me to swim, and so far i'm treading water and doing the doggie paddle lol.
That being said; here are 3 queries, I don't understand:
SELECT distinct [CustomerNumber] as '2013number'
FROM [Services]
where ServiceArea = 'Configuration' and ServiceYear = 2013 and CustomerNumber like '%320263%'
order by CustomerNumber asc /*The above returns nothing*/
SELECT distinct [CustomerNumber] as '2014number'
FROM [Services]
where ServiceArea = 'Configuration' and ServiceYear = 2014 and CustomerNumber like '%320263%'
order by CustomerNumber asc /*The above returns 2 values 320263 and 3202636*/
Select distinct CustomerNumber from ServicesPL where serviceyear = 2014 and ServiceArea = 'Configuration'
and CustomerNumber not in (Select distinct CustomerNumber from Services where serviceyear = 2013 and ServiceArea = 'Configuration' )
/*The above returns nothing*/
I do not understand how the last qurey returns nothing, I think it should return 320263 and 3202636.
Please help and thanks for helping.:-D
January 28, 2016 at 11:38 am
There's an issue when using NOT IN and the inner query returns a NULL value. In that case, it won't return any rows. You can correct that adding an additional condition in the WHERE clause.
SELECT DISTINCT CustomerNumber
FROM ServicesPL
WHERE serviceyear = 2014
AND ServiceArea = 'Configuration'
AND CustomerNumber NOT IN (SELECT CustomerNumber
FROM Services
WHERE serviceyear = 2013
AND ServiceArea = 'Configuration'
AND CustomerNumber IS NOT NULL );
You weren't seeing NULL values before because you were using LIKE which prevented any NULL values.
Personally, when possible, I prefer something like this:
SELECT [CustomerNumber] as '2014number'
FROM [Services]
WHERE ServiceArea = 'Configuration' and ServiceYear = 2014 and CustomerNumber like '%320263%'
EXCEPT
SELECT [CustomerNumber]
FROM [Services]
WHERE ServiceArea = 'Configuration' and ServiceYear = 2013 and CustomerNumber like '%320263%'
ORDER BY CustomerNumber ASC;
January 28, 2016 at 11:52 am
Thank you Luis!
I love when I find out my code is doing something stupid, I learn something new every day I'm in SQL and you have opened my eyes to another, smarter way to accomplish things!
January 28, 2016 at 12:01 pm
powerofsound (1/28/2016)
Thank you Luis!I love when I find out my code is doing something stupid, I learn something new every day I'm in SQL and you have opened my eyes to another, smarter way to accomplish things!
I wouldn't call it stupid. It's a very common problem with people that are still learning.
It's good to know that you've learned something.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply