user2493164 user2493164 - 5 months ago 7
MySQL Question

MySQL query search using joins

I have the following tables:

products table
-------------
id
name
...

categories table
----------------
id
name

product_categories table
------------------------
product_id
category_id

product_ratings
---------------
product_id
user_id
rating (1 to 5 INT)


How can I select (search) products and rating (average) by Category (name or id) and order them by Title or by rating.

I have tried some queries but im having some trouble on how to join tables and how to use where clause

Answer

Do you mean something like that?

select
    products.name, categories.name, product_ratings.rating
from products
join product_categories on products.id = product_categories.product_id
join categories on  categories.id = product_categories.category_id
join product_ratings on product_ratings.product_id = products.id
where
    categories.name = 'CATEGORY NAME'
order by
    product_ratings.rating