SELECT 3 tables in one query with condition

Question

How to choose a table in one query and show the whole product at a price?

Below is my database structure (MySql):

categories

+-------------+------------+ category_id | parent_id | +-------------+------------+ 1 | 0 2 | 1 3 | 1 4 | 1 5 | 2 6 | 3 

products_to_categories

 +-------------+------------+ product_id | category_id| +-------------+------------+ 54 | 0 55 | 2 56 | 2 57 | 2 58 | 3 59 | 3 60 | 4 

Products

 +-------------+------------+ product_id | price | +-------------+------------+ 54 | 10.50 55 | 11.20 56 | 1.00 57 | 22.20 58 | 32.0 59 | 32.0 60 | 22.0 

Below is my condition;

 1. table categories : parent_id = '1' (result : 2,3,4) 2. table products_to_categories : category_id = result categories(result : 2,3,4) (result : 55,56,57,58,59,60) 3. table products : inner join or left join table product to display price where product_id = result products_to_categories(result : 55,56,57,58,59,60) 

Final result

  55 - 11.20 56 - 1.00 57 - 22.20 58 - 32.0 59 - 32.0 60 - 22.0 

Before I write this question, here is my previous request (I got up how to go to condition 2)

 $sql_all = mysql_query("SELECT cat.parent_id,cat.category_id FROM categories cat WHERE cat.parent_id='1' "); while($row = mysql_fetch_array($sql_all)) { echo $row['categories_id'].'<br/>'; } 

thanks.

Answer 1

Try it,

 SELECT c.* FROM categories a INNER JOIN products_to_categories b ON a.category_id = b.category_id INNER JOIN products c ON b.product_id = c.product_id WHERE a.parent_id = 1 

This will display all entries from the products table with parent_id = 1.

Answer 2

Look at the following query:

 SELECT pro.product_id, SUM(pro.price) FROM categories cat JOIN products_to_categories ptc USING(category_id) JOIN products pro ON ptc.product_id = pro.product_id WHERE cat.parent_id='1' 

With the above solution, you can easily scale in the future if you want GROUP BY a specific product ...

Answer 3

The following query should bring the desired results.

 SELECT p.product_id, p.price FROM products p JOIN products_to_categories pc ON (pc.product_id = p.product_id) JOIN categories c ON ((c.category_id = pc.category_id) AND (c.parent_id = "1")) 

Of course, you can change parent_id from 1 to any other value and get the corresponding results.

Note. I believe that in this case you will get better performance by specifying the parent_id in the JOIN clause instead of the WHERE clause.