Having following tables:
OrderID 1 2 3 4 5
CustomerID OrderID 1 3 1 4 2 2 2 4 3 3 4 4
CustomerID Forename Surname 1 A Guy 2 Ted Something 3 Gaby Guy 4 Katie Kitty
the task is to select all orders made by more than one person, but only when all the persons making order have same surname. Let's say that when customers have matching surname, they are a family. So to rephrase it: We want to have all the orders all families made together (we consider a family ordering when there are two or more customers with same surname for single order).
So the expected result for dummy data above would be:
OrderID Customers 3 2
I already have a part for selecting orders that are made by more than one customer:
select OrderID, COUNT(distinct CustomerID) as CustomersCount from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1
but I struggle to implement checking, wheter they have matching surname:
select OrderID, COUNT(distinct CustomerID) as CustomersCount left join Customer on Customer.CustomerID = CustomerOrderLink.CustomerID from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1 and ???
I'll be thankful for help.