By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,607 Members | 1,159 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,607 IT Pros & Developers. It's quick & easy.

Syntax for passing two values as criteria for one field

P: 21
hi,

i have the foll query,

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT IngrID
  2. FROM IngredientDetails
  3. WHERE ProdID= (SELECT ProdID FROM ProductDetails
  4.                WHERE ProdName=('abc', 'xyz'))
it shows a syntz error near ProdName=('abc', 'xyz')) area...the same query works fine with just one prod name...i just want to know the rite syntax for mentioning both the prods in productname...for ProdName=('abc', 'xyz'))

kindly help
thanks!
May 25 '07 #1
Share this Question
Share on Google+
8 Replies

Rabbit
Expert Mod 10K+
P: 12,441
You use In () instead of = ()
May 25 '07 #2

NeoPa
Expert Mod 15k+
P: 31,770
You use In () instead of = ()
...In line #4.
May 26 '07 #3

P: 11
How does that SQL syntax work when you have not got a INNER JOIN ON for the second table?
May 26 '07 #4

NeoPa
Expert Mod 15k+
P: 31,770
How does that SQL syntax work when you have not got a INNER JOIN ON for the second table?
I'm confused. There is no second table.
May 26 '07 #5

NeoPa
Expert Mod 15k+
P: 31,770
I'm confused. There is no second table.
Ah, I understand.
The second table is within a sub-query. The sub-query returns the list of products in the specified list which are also in the [ProductDetails] table.

This also means, of course, that the query needs a bit more fixing than I'd realised.
May 26 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
The fixed code should look like :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT IngrID
  2. FROM IngredientDetails
  3. WHERE ProdID IN (SELECT ProdID FROM ProductDetails
  4.                  WHERE ProdName IN ('abc', 'xyz'))
Equally, a better way would be (Thanks for the spot Zimitry) :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ID.IngrID
  2. FROM IngredientDetails AS ID
  3.      INNER JOIN ProductDetails AS PD
  4.   ON ID.ProdID=PD.ProdID
  5. WHERE PD.ProdName IN ('abc', 'xyz')
May 26 '07 #7

P: 21
thanks all! it worked! the mist was i used = ('abc' ,'xyz')...if its more than one name it shud be IN ('abc','xyz')...i changed the same n it works now...
May 29 '07 #8

NeoPa
Expert Mod 15k+
P: 31,770
No problem Harini.
You should probably look at the alternative version though. It should be a better solution (and simpler - always good).
Good luck.
May 29 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.