[SOLVED] JOIN AND WHERE

[scottybwoy]

OK, I am familiar with JOIN AND WHERE, but not together.

 

I have two tables; one with product_ids and category_ids, and one with all the product details.

 

I want to select all product_ids for a specific category_id and filter the results based on data from the second table.  Here's MyPsudo statement that I need help with :

SELECT products.products_model, products.products_image, products.products_price
RIGHT JOIN ON products_to_categories.products_id = products.products_id
WHERE products_to_categories.categories_id AND WHERE products.products_quantity > 0 AND WHERE products.products_status = 1
ORDER BY products.products_ordered DESC LIMIT 1

 

Thats probably really basic, I'm only just beginning to do more complex queries so please bear with me.  What would be the correct syntax for that statement above, I hope it makes sense.  Cheers

[obsidian]

Let me step out on a limb here and say that a RIGHT JOIN is very seldom what you want to use for any sort of basic querying. They don't behave like people assume. You probably want to be using INNER JOIN instead. Try this:

SELECT p.products_model, p.products_image, p.products_price
FROM products p
INNER JOIN products_to_categories ptc ON ptc.products_id = p.products_id
WHERE ptc.categories_id = 'whatever'
AND p.products_quantity > 0
AND p.products_status = 1
ORDER BY p.products_ordered DESC
LIMIT 1

[scottybwoy]

Thanks Obsidian for pointing me in the right direction.