Page 1 of 1

curious sql problem

Posted: Wed Nov 15, 2006 9:34 pm
by Charles256

Code: Select all

SELECT DISTINCT products.id,products.image,products.name, products.description,products.id,products.price FROM products,subcat,deepcat,maincat WHERE (products.main_cat=13 AND maincat.id=13)
it's only part of the query but i have a record in products where main_cat is equal to 13 and there is a maincat.id equal to 13 as well.why is it returning an empty set? i'm on mysql 4.0.x

Posted: Wed Nov 15, 2006 9:39 pm
by feyd
You're joining four tables without constraining them?

Posted: Wed Nov 15, 2006 9:41 pm
by Charles256
yeah.may be the worst performance wise but shouldn't affect the results. it was working on my old server.moved it to 1&1 and now it won't work. here's the entire query

Code: Select all

SELECT DISTINCT products.id,products.image,products.name, products.description,products.price FROM products,subcat,deepcat,maincat WHERE (products.main_cat=13 AND maincat.id=13) OR (subcat.maincat_id=13 AND products.sub_cat=subcat.id) OR (subcat.maincat_id=13 AND deepcat.subcat_id=subcat.id AND products.deep_cat=deepcat.id)

Posted: Wed Nov 15, 2006 10:51 pm
by Charles256
digging has shown that the query only fails if there isn't a match in one of the category tables.what the devil?

Posted: Wed Nov 15, 2006 11:03 pm
by feyd
Your query uses implicit inner joins. Inner joins require that at least one record match in the table to be included in the record set.