SQL Statement difficulties

[andrewbadera]

SELECT * FROM bulbdirectSQL.dbo.tblProduct AS Product JOIN bulbdirectSQL.dbo.tblCategory AS Category ON Category.intCategoryID = Product.intProductID JOIN bulbdirectSQL.dbo.tblBase AS Base ON Base.intBaseID = Product.intBaseID JOIN bulbdirectSQL.dbo.tblVolts AS Volts ON Volts.intVoltsID = Product.intVoltsID JOIN bulbdirectSQL.dbo.tblWatts AS Watts ON Watts.intWattsID = Product.intWattsID WHERE '|1|' IN Product.strClassIDs

Gives me:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Product'.

/rsa/bulbdirect/search.asp, line 141

And it's definitely the WHERE clause that's causing the problem.

Any thoughts?

 

[bperry]

>>WHERE '|1|' IN Product.strClassIDs

When using the IN predicate, you normally need to provide a list of values, like this:

WHERE '|1|' IN ('war', 'abc', '|1|', 'A41')

Also, a subquery can be used to provide the list of values:
WHERE '|1|' IN (Select strClassIDs From dbo.tblProduct)

Not sure exactly what you need here, but perhaps this will work?
WHERE Product.strClassID = '|1|'
--------------------------------

bp

 

[andrewbadera]

I changed it to field LIKE '%|1|%' and that works.