Inner join of 4 tables

Question

Tables

Production

Category

Orders

OrderDetails

I've made SalesView as follow

create view salesView as select o.oID, p.name as product, od.sell_price as price, od.qty as quantity, o.order_date
    from orderDetails od inner join
    orders o on o.oID = od.oID,
    production p where = p.ID = od.pID;

As od ( instance of Table OrderDetails ) does not contains reference to Category table. How can I add column 'Category' in SalesView?

Now,

select * from SalesView;

I want to add 'Category' column in it.

I've tried...

create view salesView as select o.oID, p.name as product, c.name as category, od.sell_price as price, od.qty as quantity, o.order_date
    from orderDetails od inner join
    orders o on o.oID = od.oID,
    production p where = p.ID = od.pID,
    category c where c.ID = ???

Answer 1

Don't mix notations/standards. To add category, simply inner join category to your production table. This assumes all productions will have a category; otherwise you may want to left (outer) join.

create view salesView as 
SELECT o.oID
     , p.name as product
     , c.name as category
     , od.sell_price as price
     , od.qty as quantity
     , o.order_date
     , c.name
FROM orderDetails od 
INNER JOIN orders o 
   on o.oID = od.oID
INNER JOIN Production p 
   on p.ID = od.pID
INNER JOIN Category c
   on c.ID = P.catID
WHERE...

Don't do this:

create view salesView as select o.oID, p.name as product, od.sell_price as price, od.qty as quantity, o.order_date
    from orderDetails od inner join --<See the inner join
    orders o on o.oID = od.oID, --<See the ,  (don't mix standards!)
    production p where = p.ID = od.pID;

Notice the difference in standards: https://gerardnico.com/data/type/relation/sql/join_default_ansi92_comparison