curious sql problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

curious sql problem

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're joining four tables without constraining them?
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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)
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

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