2 table SQL Query weird results

Question

Ok this is driving me nuts, I need to write an SQL query that will grab product information from 2 tables. The first table 'products' contains the productId, productname, quantityperunit and unitprice. Now I can search by productname and categoryname individually, but when I try to combine the 2 I get crazy results, Here's the query:

"SELECT DISTINCT productId, productname, quantityperunit, unitprice FROM products pr,
categories ca WHERE pr.categoryID = ca.categoryID AND ProductName LIKE '%" + searchTerm + "%'
OR CategoryName LIKE '%" + searchTerm + "%'

excuse the java style in there, here it is formatted better:

SELECT DISTINCT productId, productname, quantityperunit, unitprice FROM products pr,
categories ca WHERE pr.categoryID = ca.categoryID AND ProductName LIKE '%Tofu%'
OR CategoryName LIKE '%Tofu%'

any help would be appreciated.

Answer 1

Your LIKEs in your two sample queries are different.

LIKE '%Tofu%' is very different from LIKE 'Tofu'.

The % acts like a wildcard, so LIKE '%Tofu%' matches Big chunk o' Tofu and stuff but LIKE 'Tofu' will not match that - it will ONLY match Tofu.

Which did you mean?

(also, I agree with the guy who mentioned you should check your brackets)

Answer 2

I cannot tell for sure what you mean by "crazy reslust", but I guess you got many more rows than you expected. Add parenthesis :

WHERE pr.categoryID = ca.categoryID AND (ProductName LIKE '%Tofu%'
OR CategoryName LIKE '%Tofu%')