没有订单的sql订单没有给出结果

Question

我想要的结果是关于一个订单的信息。当我的订单有一些订单项时，以下查询工作正常，但只要它是空订单，它就不会提供任何信息。例如我的orderid = 5，这个还没有任何项目。我使用订单项来计算订单的总成本。如何在不存在订单项时获取我的信息？这是我的查询：

SELECT DISTINCT 
    u.firstname|| ' ' || u.lastname, 
    TO_CHAR(o.date,'DD-MON-YY HH24:mm'), 
    (SELECT SUM(oItem.amount * oItem.pricewhenordered) FROM ORDERITEM oItem WHERE 
    oItem.ORDERID=o.ID), 
    (case o.employeeid 
    when null then '-' 
    else (SELECT us.firstname|| ' ' || us.lastname FROM user us WHERE 
    id=o.employeeid) 
    END), 
    o.status 
FROM order o, orderitem oItem, user u 
WHERE o.userid = u.id 
AND o.id = oItem.orderid 
AND o.id = 5; 

TABLES
order ID， 用户ID， 日期， 状态， 雇员

orderitem 的OrderID， 的productid， 量， pricewhenordered

user ID，用户名 ， 密码， 名字，姓氏 ， usertypeid

Answer 1

你实际上并没有在你的主查询中使用ORDERITEM（oitem）表;只是不要说：

SELECT DISTINCT 
    u.firstname|| ' ' || u.lastname, 
    TO_CHAR(o.date,'DD-MON-YY HH24:mm'), 
    (SELECT SUM(oItem.amount * oItem.pricewhenordered) 
    FROM ORDERITEM oItem 
    WHERE oItem.ORDERID=o.ID), 
    (case o.employeeid 
     when null 
     then '-' 
     else (SELECT us.firstname|| ' ' || us.lastname 
       FROM user us 
       WHERE id=o.employeeid) 
     END), 
    o.status 
FROM 
    order o, user u 
WHERE 
    o.userid = u.id 
    AND o.id = 5; 

Answer 2

显式说明联接和使用LEFT在没有任何项目应该做的工作的情况下加入。

SELECT DISTINCT 
    u.firstname|| ' ' || u.lastname, 
    TO_CHAR(o.date,'DD-MON-YY HH24:mm'), 
    (SELECT SUM(oItem.amount * oItem.pricewhenordered) FROM ORDERITEM oItem WHERE 
    oItem.ORDERID=o.ID), 
    (case o.employeeid 
    when null then '-' 
    else (SELECT us.firstname|| ' ' || us.lastname FROM user us WHERE 
    id=o.employeeid) 
    END), 
    o.status 
FROM order o 
LEFT OUTER JOIN orderitem oItem ON o.id = oItem.orderid 
INNER JOIN user u ON o.userid = u.id 
WHERE o.id = 5;