A SQL question

heyday2004
heyday2004 used Ask the Experts™
on
This should be a typical SQL question:

Suppose I have a Product table with two columns: accountNum and name:

accountNum                name
1                                  Apple
2                                  Orange
2                                  Banana
3                                  Apple
4                                  Banana

Of course, the real table has much more data.

Now I want to get a list of accountNum where there is no name of Apple in the WHOLE table for this accountNum, I used below query:

select distinct accountNum from Product
where name not in
(
'Apple'
)
 
What I wanted to get is accountNum: 4. But sometimes,  I just got other accountNum, like 3, etc. It's obviously not working. Any explanation? Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Umar Topia.Net Full Stack Developer
Commented:
select distinct accountNum from Product where name not like '%Apple%'
Umar Topia.Net Full Stack Developer
Commented:
Please ignore my previous comment... I misread the question
Umar Topia.Net Full Stack Developer
Commented:
SELECT DISTINCT AccountNum FROM Product WHERE AccountNum NOT IN
(SELECT DISTINCT AccountNum from Product WHERE Name = 'Apple')
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Hi,
The correct syntax for the sql is the one that umartopia gave you.
The reason that you initial sql was not working as you thought it would was because the way it was working was the following
1) take out all the records where the name is 'Apple'
so that would leave you with the following data
accountNum                name
2                                  Orange
2                                  Banana
4                                  Banana

2) Give me the distinct ids from the previous  results so  that would return 2,4.

I hope I helped you understand the problem in your initial sql.

Author

Commented:
This is what I meant. But is there any simpler, faster solution? Performance is important for me. Thanks a lot.
It really depends on what your after.

By using DISTINCT you are only selecting one of every type in the table, so if you had two records where name is equal to 'Banana' then the result would only display one of them.  

So you can remove the DISTINCT if you want duplicates.

and as umartopia says using the NOT IN works very well, or you can use the <> (not equal to) method which probably has a smaller overhead on larger tables.


SELECT DISTINCT AccountNum FROM Product WHERE name <> 'Apple'

Author

Commented:
Thanks for the detailed explanation.
Re-reading the question, you want to get the account num 4 out?

If this is so, you want the last reference of 'Apple' in the table ?

If this is so, you need to select a list of only the apples, then reverse the order and select the top 1 item.

The code below assumes the account numbers are in ascending order to start

SELECT TOP 1 AccountNum from Product WHERE name = 'Apple' order by AccountNum Desc

This will give you the last record that is Apple.
Commented:
If performance is of an issue then you could go with the following syntax
SELECT DISTINCT AccountNum FROM Product P1 WHERE not exists
(SELECT 1 from Product P2 WHERE P2.Name = 'Apple' and P2.AccountNum=P1.AccountNum)
what it is going to do is the following
1) Select the distinct id from the table so 1,2,3,4 and then
2) Check that this AccountNum doesn't have any Apple references with the not exist check so that would return 2,4
Exists is generally quicker that In.
How are your indexes on this table? Those are going to improve performance also.
Umar Topia.Net Full Stack Developer
Commented:
You can try out Self JOIN

Commented:
why not make it simple:
Textbox1.Text = "APPLE"
dim ss as string = "select accountnum from product where name<>@name"
dim sc as new sqlcommand(ss, yourcon)
sc.parameters.Addwithvalue("@name", Textbox1.Text.ToUpper)
dim sr as sqldatareader=sc.executereader
while sr.read
console.writeln({0}, sr.item("accountnum"))
End while
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
See if this one performs better.  It should only hit the table once.

select distinct accountnum from
(
select accountnum, sum(case when name='Apple' then 1 end) over(partition by accountnum) has_apple
from tab1
)
where has_apple is null
/

Open in new window

awking00Information Technology Specialist
Commented:
>>What I wanted to get is accountNum: 4.<<
Why did you not want accountNum: 2? Is it because it is not distinct?
If that's the case then
select accountNum from
(select accountNum, count(*)
 from table
 where name <> 'Apple'
 group by accountNum
 having count(*) = 1)
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
awking00,

Your SQL will fail with data like:
1                                  Apple
1                                  Orange
awking00Information Technology Specialist
Commented:
You're right. Adding
minus
select accountNum from table where name = 'Apple'
should work.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>Adding minus

The you are back to hitting the table twice.  Mine only hits the table once.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial