CODEWITHSUNDEEP
sqloracle-databaseview
Geno
Geno

Reputation: 13

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

Upvotes: 0

Views: 69

Answers (2)

MT0
MT0

Reputation: 167877

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

Upvotes: 1

dougp
dougp

Reputation: 3089

I do this conversion almost daily. I think the folks that wrote the SQL for one of the applications I work with have extensive experience in VB. (not SQL)

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

Upvotes: 0

Related Questions