Hi,
I’m trying to select product data from two tables and filter by category. Here’s an idea of the tables:
products
[TABLE=“class: outer_border, width: 200”]
[TR]
[TD]product_id[/TD]
[TD]model_number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[/TABLE]
product_description
[TABLE=“class: outer_border, width: 200”]
[TR]
[TD]product_id[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]lesabre[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]invicta[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]impala[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]caprice[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]dart[/TD]
[/TR]
[/TABLE]
category_lookup
[TABLE=“class: outer_border, width: 200”]
[TR]
[TD]product_id[/TD]
[TD]category_id[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[/TABLE]
My base select to pull the list of all products is this, and it works:
SELECT * FROM products, product_description WHERE products.product_id > 0 && products.product_id = product_description.product_id ORDER BY model DESC LIMIT 0,30
But when I try to select only products from a particular category, things go awry. I tried the following, but it resulted in a real slow query and instead of about 30 results I got thousands! (the real database has about 300 items total)
SELECT * FROM products, product_description, category_lookup WHERE products.product_id > 0 && products.product_id = product_description.product_id AND (products.product.id = category_lookup.product.id AND category_lookup.category_id = 3) ORDER BY model DESC LIMIT 0,30
thanks for any suggestions.
-takayuki