select from 2 tables and 1 lookup table ??

[takayuki]

Hi,

I'm trying to select product data from two tables and filter by category. Here's an idea of the tables:

products

product_id	model_number
1
2
3
4
5

product_description

product_id	name
1	lesabre
2	invicta
3	impala
4	caprice
5	dart

category_lookup

product_id	category_id
1	2
2	3
3	2
4	1
5	3

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

[r937]

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

[takayuki]

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

[r937]

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

[takayuki]

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