Using Join operations in SQL-CodePudding

I'm expected to work with SQL and don't understand the use of joint operator. My objective is to retrieve 3 columns for each ice cream: ice_cream_id, ice_cream_name, manufacturer_name. Those should put in ascending order based on ice_cream_id. So for example if I have Plain Vanilla ice cream, I don't want to identify with all 3 manufacturers but only number one.

I understand SELECT and FROM commands, but not the joint operator in WHERE: SELECT ice_cream_id, ice_cream_name, manufacturer_name FROM ice_cream, manufacturer .

Additionally I should retrieve id, name, manufacturing cost and manufacturer name for all ice creams where manufacturing cost is greater than 1. I think I should use WHERE for this as well?

manufacturer
manufacturer_id  manufacturer_name  country
---------------  -----------------  ----------
1                Ben & Jerry's      Canada
2                4 Friends          Finland
3                Gelatron           Italy

ice_cream
ice_cream_id  ice_cream_name    manufacturer_id  manufacturing_cost
------------  ----------------  ---------------  ------------------
1             Plain Vanilla     1                1
2             Vegan Vanilla     2                0.89
3             Super Strawberry  2                1.44
4             Very plain        2                1.2



ingredient
ingredient_id  ingredient_name  kcal        protein     plant_based
-------------  ---------------  ----------  ----------  -----------
1              Cream            400         3           0
2              Coconut cream    230         2.3         1
3              Sugar            387         0           1
4              Vanilla extract  12          0           1
5              Strawberry       33          0.7         1
6              Dark chocolate   535         8           1


contains
ice_cream_id  ingredient_id  quantity
------------  -------------  ----------
1             1              70
1             3              27
1             4              3
2             2              74
2             3              21
2             4              5
3             1              60
3             3              10
3             5              30
4             2              95
4             4              5

CodePudding user response：

SELECT ice_cream_id, ice_cream_name, manufacturer_name from manufacturer JOIN 
contains ON manufacturer.ice_cream_id = contains.ice_cream_id LEFT JOIN ingredient 
ON contains.ingredient_id = ingredient.ingredient_id ORDER BY ice_cream_id ASC;

Please try this and see if it helps. Let me know if it doesn't. I'll help you.

CodePudding user response：

With join you can combine rows from two or more tables.

In your case:

SELECT i.ice_cream_id, i.ice_cream_name, m.manufacturer_name
FROM ice_cream i INNER JOIN manufacturer m ON i.manufacturer_id = m.manufacturer_id
ORDER BY i.ice_cream_id;

With INNER JOIN you can combine rows where in both tables there needs to be a value. So NULL is not valid.

CodePudding user response：

You do not need to JOIN two tables in the where clause. Do it like this:

SELECT ic.ice_cream_id, ic.ice_cream_name, mf.manufacturer_name 
FROM ice_cream ic
JOIN manufacturer mf on ic.manufacturer_id = mf.manufacturer_id

In this example ic and mf are aliases of the tables you use. You can use different JOIN commands depending on what you want to do(like LEFT JOIN or RIGHT JOIN...). To JOIN two tables you use columns that are the same in both tables. Sometimes you need to use more than one column to join two tables...

With this new information I believe you should now try yourself to do the second part: "Additionally I should retrieve id, name, manufacturing cost and manufacturer name for all ice creams where manufacturing cost is greater than 1. I think I should use WHERE for this as well?"

CodePudding user response：

you can use these two queries to get the result set that you want.

        --retrieve 3 columns for each ice cream
        SELECT ice.ice_cream_id,
               ice.ice_cream_name,
               man.manufacturer_name
        FROM manufacturer AS man
            INNER JOIN ice_cream AS ice
                ON ice.manufacturer_id = man.manufacturer_id
        ORDER BY ice.ice_cream_id ASC;
        ---Costs greater than 1
        SELECT *
        FROM
        (
            SELECT man.manufacturer_id,
                   ice.ice_cream_name,
                   man.manufacturer_name,
                   SUM(ice.nufacturing_cost) AS manufacturing_cost
            FROM manufacturer AS man
                INNER JOIN ice_cream AS ice
                    ON ice.manufacturer_id = man.manufacturer_id
            GROUP BY man.manufacturer_id,
                     man.manufacturer_name,
                     ice.ice_cream_name
        ) AS TotalCost
        WHERE TotalCost.manufacturing_cost > 1;