Search code examples
sqlselect

how to select customer having relations of only one condition

I have dbo.Customers and dbo.CustomerRelations tables.

dbo.Customers:

CustomerId, CustomerType

dbo.CustomerRelations:

MainCustomerId, RelatedCustomerId

for example main customers have many RelatedCustomers of different types:

MainCustomerId RelatedCustomerId RelatedCustomerType
55 111 1
55 112 1
55 113 4
55 114 3
55 115 3
55 116 1
66 661 1
66 662 1
77 771 1
77 772 3
88 881 1
88 882 1
88 883 1

In result, I want to get main customers, to whom are related only customers, having customer type for example 1. According to my example given above, i want to write a query that will give me customers: 88 and 66, who have only that related customers, who have type 1. I don't want to get other main customers, that have relations of different customer types including type 1, but i want to get only that main customers, who have only that relations having type 1.

thank you.

Solution 
  • SELECT MainCustomerId
FROM mytable
GROUP BY MainCustomerId
HAVING COUNT(CASE WHEN RelatedCustomerType = 1 THEN 1 END) >= 1
       and COUNT(CASE WHEN RelatedCustomerType <> 1 THEN 1 END) = 0

    We need to select customers validating two conditions :

    1. Having records with type 1 : COUNT(CASE WHEN RelatedCustomerType = 1 THEN 1 END) >= 1
    2. Do not having any other type different than 1 : COUNT(CASE WHEN RelatedCustomerType <> 1 THEN 1 END) = 0