Link to home
Start Free TrialLog in
Avatar of heyday2004
heyday2004

asked on

A SQL question

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.
SOLUTION
Avatar of Umar Topia
Umar Topia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of heyday2004
heyday2004

ASKER

This is what I meant. But is there any simpler, faster solution? Performance is important for me. Thanks a lot.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the detailed explanation.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial