Jump to content

How do i cross join , Inner join and Group tables


sashavalentina

Recommended Posts

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

image.png.530010fe83840f6a46da5bea1d63593d.png

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              |

Link to comment
Share on other sites

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          |
+----+----------+----+--------------+----+---------------+

 

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.