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.