mysql - 4个表的内部联接

桌子

生产



类别



订单



订单详细信息



我做了如下的SalesView

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;

由于od（Table OrderDetails的实例）不包含对Category表的引用。如何在SalesView中添加“类别”列？

现在，

select * from SalesView;

我想在其中添加“类别”列。

我试过了...

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 = ???

最佳答案

不要混用符号/标准。要添加类别，只需将内部联接类别添加到生产表即可。假设所有作品都有一个类别；否则，您可能想离开（外部）加入。

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

不要这样做：

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;

注意标准之间的差异：https://gerardnico.com/data/type/relation/sql/join_default_ansi92_comparison