SQL to pick only one record in one-to-many relationship

Question

In the following query of current/perspective customers, I need to display, CustomerID, Customer's LastName, along with a column that displays whether customer has placed at least one order or not. But, as expected, it displays multiple records of a customer if the customer placed multiple orders (one-to-many relationship). Question: How can we display only one record per customer here since we need only to report whether or not a customer has placed at least one order?

SELECT c.customerID, o.OrderID, CASE When ISNULL(o.OrderID, 0) = 0 Then 0 Else 
1 End as YesNO
FROM Customers c
LEFT JOIN Orders o
 ON c.customerID = o.customerID

Instead of having the CASE statement why not just use a count to check if the number of orders is greater or equal to 1 — kyle, May 02 '17 at 17:40
`SELECT c.customerID, min(o.OrderID), case when coalesce(count(o.orderID),0) >0 then 1 else 0 end from ... group by c.customerID, o.OrderID` — xQbert, May 02 '17 at 17:43
@xQbert that would return multiple rows because you are still grouping by `o.OrderId`... using `min(o.OrderID)` (or `max()`) would fix that. — SqlZim, May 02 '17 at 17:46
@sqlzim... This is true... `SELECT c.customerID, min(o.OrderID), case when coalesce(count(o.orderID),0) >0 then 1 else 0 end from ... group by c.customerID` and remove o.orderId from group by! — xQbert, May 02 '17 at 17:49
Does "Question: How can we display only one record per customer here since we need only to report whether or not a customer has placed at least one order?" mean that you _don't_ need to return `OrderId`, just `YesNo`? — HABO, May 03 '17 at 20:19
@HABO Agreed. `o.OrderId` in the example is just to verify the validity of `Yes` or 'No` (i.e. 1 or 0) in the display - meaning if ``o.OrderId`` is null is it really displaying 0 (otherwise 1). — nam, May 03 '17 at 20:53
In that case the answer I posted should be rather more efficient since it only uses `exists`. You may want to compare the actual query plan to the other answers to see what the difference is. — HABO, May 03 '17 at 21:05

SqlZim · Accepted Answer · 2017-05-02T17:45:57.863

2

using outer apply()

select 
    c.customerID
  , o.OrderID
  , case when o.OrderID is null then 0 else 1 end as YesNO
from Customers c
  outer apply (
    select top 1 o.OrderID
    from Orders o
    where c.customerID = o.customerID
  ) o

You could also use o.OrderId is null instead of ISNULL(o.OrderID, 0) = 0.

using group by and min()

select 
    c.customerID
  , min(o.OrderID) as OrderId
  , case when min(o.OrderID) is null then 0 else 1 end as YesNO
from Customers c
  left join Orders o 
    on c.customerID = o.customerID 
group by c.CustomerID

edited May 02 '17 at 17:45

answered May 02 '17 at 17:39

SqlZim

37,248
6
41
59

Thank you and the SO founders and their team for spreading knowledge all round the globe. It's like people asking: I can help you, can I give you my helping hand. One note: Probably `o.OrderId is null instead of ISNULL(o.OrderID, 0) = 0` will not work as explained [here](http://stackoverflow.com/questions/43743507/t-sql-case-clause-issue-on-specifying-when-null-with-an-outer-join). – nam May 02 '17 at 18:04
@nam The answer to your previous question regarding `null` that you linked to is incorrect. Here is a simple example: http://rextester.com/NCJR80179 – SqlZim May 02 '17 at 18:08
But using `o.OrderId is null` in that [post](http://stackoverflow.com/questions/43743507/t-sql-case-clause-issue-on-specifying-when-null-with-an-outer-join) example always gives me `1` regardless of whether `o.OrderID` is null or not. I think using `o.OrderId is null` probably does not work in case of OUTER Join. I think SQL compiler may be assuming `o.OrderID` being a PK is `not null`. This may work when not using OUTER Join as in this SO post [example](http://stackoverflow.com/a/3237670/1232087). – nam May 02 '17 at 20:38
@nam I believe there is a different root cause for your issue with `is null`, as the result you are describing does not fit the situation. You have not provided an MVE https://stackoverflow.com/help/mcve – SqlZim May 02 '17 at 20:41

score 0 · Answer 2 · answered May 02 '17 at 17:40

0

Use the group by.

SELECT c.customerID, o.OrderID, CASE When ISNULL(o.OrderID, 0) = 0 Then 0 Else 1 End as YesNOFROM Customers cLEFT JOIN Orders o ON c.customerID = o.customerID GROUP BY c.customerID

answered May 02 '17 at 17:40

berend

553
2
12

Close, but doesn't solve the multiple records per customer problem. generally we don't group by without an aggregate if duplication exists a distinct would make more sense. and in sql server group by has to contain all non-aggregrated columns, unlike mySQL. – xQbert May 02 '17 at 17:47

score 0 · Answer 3 · answered May 02 '17 at 18:20

If the description in your question is to be believed, i.e. you want to know whether a customer has placed an order but do not need a representative OrderId for each customer:

select C.CustomerId,
  case when exists ( select 42 from Orders as O where O.CustomerId = C.CustomerId )
    then 1 else 0 end as YesNo
  from Customers as C;

Note that exists is more efficient than count when you don't need an exact number.

SQL to pick only one record in one-to-many relationship

3 Answers3