Select from 2 tables and 1 lookup table?

Databases
1

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

2 
SELECT products.id
     , products.model_number 
     , product_description.name
  FROM category_lookup
INNER
  JOIN products
    ON products.product.id = category_lookup.product_id
INNER
  JOIN product_description
    ON product_description.product_id = products.product_id
 WHERE category_lookup.category_id = 3 
ORDER 
    BY products.model_number DESC LIMIT 0,30
3

thanks r937. I did some reading up on inner joins and i see what’s happening.

your select statement works great, but when I want to list all items (remove the WHERE clause), and not specify a particular category, I get two of each item in the database.

What should I add to get a full listing without doubles?

thanks,

takayuki

4

you’re sorting by product model, and if a product belongs to more than one category, you’ll see it that many times

if you just want to list all items, then perhaps use a separate query which doesn’t involve the categories

5

roger that. thanks again. i’ve learned alot!