like statement returns unexpected results

[GGR_Web]

Hi

I have this mysql query:

SELECT DISTINCT productgroup.id FROM productgroupmenu, productgroup, product WHERE productgroupmenu.productgroupid = productgroup.id = product.productgroup AND product.partno LIKE '%GGR%' AND product.partno NOT LIKE '%GGRM%'

This is the problem
product.partno LIKE '%GGR%'
Which is returning stuff like 'st175' which I thought like wouldn't do. (when I do this though my script instead of phpmyadmin the alphabetic characters are striped when echoed)

Can someone see where I'm going wrong?

[whoisgregg]

I believe the problem is with how MySQL is executing the query. An EXPLAIN would probably help quite a bit, but in the meantime, try this query and see if it returns the found set you expect:

SELECT 
product.partno 
FROM product 
WHERE (product.partno LIKE '%GGR%' AND product.partno NOT LIKE '%GGRM%') 

If this returns the set you expect, then the problem is with the join. This is where I suspect the problem exists. When I write joins, I try to write the entire query in one "direction." Basically starting with the table where I perform the most limiting query on (product in this case) then placing the queries which join that found set to the related tables.

Something like this might be worth a shot:

SELECT 
DISTINCT productgroup.id 
FROM product, productgroup, productgroupmenu 
WHERE (product.partno LIKE '%GGR%' AND product.partno NOT LIKE '%GGRM%') 
AND product.productgroup = productgroup.id
AND productgroup.id = productgroupmenu.productgroupid 

If neither is helpful, please do an EXPLAIN and post back the results. :)