Syntax for passing two values as criteria for one field

[harini]

hi,

i have the foll query,

SELECT DISTINCT IngrID
FROM IngredientDetails
WHERE ProdID= (SELECT ProdID FROM ProductDetails
               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!

[Rabbit]

You use In () instead of = ()

[NeoPa]

You use In () instead of = ()

...In line #4.

[zimitry]

How does that SQL syntax work when you have not got a INNER JOIN ON for the second table?

[NeoPa]

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.

[NeoPa]

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.

[NeoPa]

The fixed code should look like :

SELECT DISTINCT IngrID
FROM IngredientDetails
WHERE ProdID IN (SELECT ProdID FROM ProductDetails
                 WHERE ProdName IN ('abc', 'xyz'))

Equally, a better way would be (Thanks for the spot Zimitry) :

SELECT DISTINCT ID.IngrID
FROM IngredientDetails AS ID
     INNER JOIN ProductDetails AS PD
  ON ID.ProdID=PD.ProdID
WHERE PD.ProdName IN ('abc', 'xyz')

[harini]

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...

[NeoPa]

No problem Harini.
You should probably look at the alternative version though. It should be a better solution (and simpler - always good).
Good luck.