How to insert a inner join from 3 different tables

I am making a view pulling data from 3 different tables for the query. Here is my code so far, I’m just not sure how to integrate the inner joins since I already have a select statement, I can’t picture it. A rewrite of my code would be appreciated!

AS SELECT c.nutritional_value, i.item_id, i.item_name,
m.sell_price, m.buy_price
FROM consumables c, items i, merchants m
WHERE c.item_id=i.item_id
AND c.item_id=m.item_id```

Answer

You already have an inner join; you just wrote it using the legacy comma join syntax rather than the (more modern) ANSI syntax.

SELECT c.nutritional_value,
       i.item_id,
       i.item_name,
       m.sell_price,
       m.buy_price
FROM   consumables c,         -- The comma indicates a CROSS JOIN
       items i,               -- The comma indicates a CROSS JOIN
       merchants m
WHERE  c.item_id=i.item_id    -- This filter condition implicitly converts the first join to
                              -- an INNER JOIN
AND    c.item_id=m.item_id    -- This filter condition implicitly converts the second join to
                              -- an INNER JOIN

If you want to explicitly rewrite it using ANSI syntax then:

• replace the commas with the join type; then
• after the identifier for the table being joined add an ON clause containing the join condition.

SELECT c.nutritional_value,
       i.item_id,
       i.item_name,
       m.sell_price,
       m.buy_price
FROM   consumables c
       INNER JOIN items i     ON c.item_id=i.item_id
       INNER JOIN merchants m ON c.item_id=m.item_id