Include subselect only if there is one result using tsql

We have an invoice, a invoice detail and a order table and the tables are linked by the invoice detail rows because the invoice details are grouped by delivery date so a invoice often covers multiple order numbers.

Now I would like to build a view that would display the order number if there is only one order involved in the invoice by using a subselect of some kind.

I came up with this one but it still generates an error reporting that the subqu开发者_高级运维ery return more than one result

SELECT Invoice.Id, Invoice.TotalAmount, 
(SELECT DISTINCT OrderId  FROM InvoiceDetail 
WHERE InvoiceDetail.InvoiceId = Invoice.Id
GROUP BY OrderId HAVING COUNT(DISTICT OrderId) = 1) AS OrderId 
FROM Invoice

Any ideas to get this to work?

---

How about:

SELECT 
  Invoice.Id,
  Invoice.TotalAmount,
  OneOrder.OrderId
FROM 
  Invoice
  LEFT JOIN (
    SELECT   InvoiceId, MIN(OrderId) OrderId
    FROM     InvoiceDetail 
    GROUP BY InvoiceId
    HAVING   COUNT(DISTINCT OrderId) = 1
  ) OneOrder ON OneOrder.InvoiceId = Invoice.Id

---

Tested correct:

SELECT Id, TotalAmount, OrderInfo.OrderId 
  FROM Invoice 
  JOIN
    (
      SELECT InvoiceId, OrderId 
        FROM InvoiceDetail 
        JOIN Invoice  
          ON InvoiceDetail.InvoiceId = Invoice.Id  
       GROUP BY InvoiceId, OrderId
      HAVING COUNT(OrderId)=1
    ) AS OrderInfo
    ON Invoice.Id=OrderInfo.InvoiceId

Notice lack of DISTINCT in HAVING clause, which is incorrect (it would cause multiple order ids to count as one, breaking the expected behavior)

---

Change GROUP BY OrderId to GROUP BY InvoiceDetail.InvoiceId

---

Your problem may just be the typo in the HAVING clause. See DISTICT.