How do i cross join , Inner join and Group tables

[sashavalentina]

I have 3 tables which are order tables , product tables and category_tables .

I want to get the result of the category Meat only from the order_tables by cross joining it with the product tables and the category_tables . But why do i always get the result of the first product with the category i selected. This is the sql i use to select the Meat category

SELECT * FROM ( (SELECT * FROM order_table) as yw1 INNER JOIN (SELECT * FROM products) as yw2 ON yw1.product_id = yw2.id CROSS JOIN (SELECT * FROM category) as yw5 ON yw2.product_category = yw5.id) GROUP BY category = "Meat"

For example, i selected the Meat category it will show the result of

| id | order_id | product_id | product_name | id | category_name |
| 1 | 10001      | 1                 | carrot                | 1 |      Tubers           |
| 4 | 10004     | 4                 | Beef                 | 4 |      Meat              |

[Barand]

You are grouping by the condition category_name = 'Meat'

This has two possible values (0 , 1) so you get 1 row for each value. Tubers just happens to be one of those where the condition evaluates to 0.

You should GROUP BY <column name>

Don't use SELECT *. Specify column names that you need.

CROSS JOIN gives every combination of rows in table A joined with rows in table B so why would you want that here? For example

Table A    TableB
  -------    ------
     a         1
     b         2
     c         3
     
  SELECT a.col, b.col
  FROM tableA a CROSS JOIN tableB b;
  
    a  1
    a  2
    a  3
    b  1
    b  2
    b  3
    c  1
    c  2
    c  3

You seem to have gone overboard with unnecessary subqueries too.

Try

mysql> SELECT o.id
    ->        , o.order_id
    ->        , p.id
    ->        , p.product_name
    ->        , c.id
    ->        , c.category_name
    -> FROM order_table o
    ->      INNER JOIN product p ON o.product_id = p.id
    ->      INNER JOIN category c ON p.product_category = c.id
    -> WHERE category_name = 'meat';
+----+----------+----+--------------+----+---------------+
| id | order_id | id | product_name | id | category_name |
+----+----------+----+--------------+----+---------------+
|  4 | 10004    |  4 | Beef         |  4 | Meat          |
+----+----------+----+--------------+----+---------------+