CODEWITHSUNDEEP
sqlsql-serverunique
welshsteve147
welshsteve147

Reputation: 77

Select records that are only associated with a record in another table

Not sure if the title explains this scenario in full, so I will be as descriptive as I can. I'm using a SQL Server database and have the following 4 tables:

CUSTOMERS:

CustomerID   CustomerName
--------------------------
100001       Mr J Bloggs
100002       Mr J Smith

POLICIES:

PolicyID  PolicyTypeID  CustomerID
-----------------------------------
100001    100001        100001
100002    100002        100001
100003    100003        100001
100004    100001        100002
100005    100002        100002

POLICYTYPES:

PolicyTypeID   PolTypeName   ProviderID
-----------------------------------------
100001         ISA           100001
100002         Pension       100001
100003         ISA           100002

PROVIDERS:

ProviderID    ProviderName
--------------------------
100001        ABC Ltd
100002        Bloggs Plc

This is obviously a stripped down version and the actual database contains a lot more records. What I am looking to do is return a list of clients who ONLY have products from a certain provider. So in the example above, if I want to return customers who have policies with ABC Ltd with this SQL:

SELECT 
    C.CustomerName, P.PolicyID, PT.PolTypeName, Providers.ProviderName
FROM 
    Customers C 
LEFT JOIN 
    Policies P ON C.CustomerID = P.CustomerID
LEFT JOIN 
    PolicyTypes PT ON P.PolicyTypeID = PT.PolicyTypeID
LEFT JOIN 
    Providers PR ON PR.ProviderID = PT.ProviderID
WHERE 
    PR.ProviderID = 100001

It will currently return both customers in the Customers table. But the customer Mr J Bloggs actually holds policies provided by Bloggs Plc as well. I don't want this. I only want to return the customers who hold ONLY policies from ABC Ltd, so the SQL I need should only return Mr J Smith.

Hope I've been clear, if not please let me know.

Many thanks in advance

Steve

Upvotes: 3

Views: 79

Answers (3)

Rohit Patel
Rohit Patel

Reputation: 11

try this one...

SELECT C.CustomerName, P.PolicyID, PT.PolTypeName, Providers.ProviderName 
    from Customers C inner join POLICIES P ON C.CustomerID = P.CustomerID
    inner join PT ON P.PolicyTypeID = PT.PolicyTypeID
    inner join Providers PR ON PR.ProviderID = PT.ProviderID
    where  PR.ProviderID = 100001 and c.CustomerID not in 
    (SELECT C.CustomerID from Customers C 
    inner join POLICIES P ON C.CustomerID = P.CustomerID 
    inner join PT ON P.PolicyTypeID = PT.PolicyTypeID 
    inner join Providers PR ON PR.ProviderID = PT.ProviderID where  PR.ProviderID <> 100001)

Upvotes: 1

Captain Kenpachi
Captain Kenpachi

Reputation: 7215

Tthe idea is that you additionally perform a NOT IN on customerid's that are linked to other providers:

SELECT C.CustomerName, P.PolicyID, PT.PolTypeName, Providers.ProviderName
FROM Customers C LEFT JOIN Policies P ON C.CustomerID = P.CustomerID
LEFT JOIN PolicyTypes PT ON P.PolicyTypeID = PT.PolicyTypeID
LEFT JOIN Providers PR ON PR.ProviderID = PT.ProviderID
WHERE PR.ProviderID = 100001
--NEW PART
AND C.CustomerID NOT IN
(
    SELECT P.CustomerID
    FROM Policies P
    LEFT JOIN PolicyTypes PT ON P.PolicyTypeID = PT.PolicyTypeID
    LEFT JOIN Providers PR ON PR.ProviderID = PT.ProviderID
    WHERE PR.ProviderID <> 100001
)

Upvotes: 1

Smutje
Smutje

Reputation: 18153

Dirty but readable:

SELECT C.CustomerName, P.PolicyID, PT.PolTypeName, Providers.ProviderName
FROM Customers C LEFT JOIN Policies P ON C.CustomerID = P.CustomerID
LEFT JOIN PolicyTypes PT ON P.PolicyTypeID = PT.PolicyTypeID
LEFT JOIN Providers PR ON PR.ProviderID = PT.ProviderID
WHERE PR.ProviderID = 100001 AND C.CustomerName NOT IN (
    SELECT C.CustomerName
    FROM Customers C LEFT JOIN Policies P ON C.CustomerID = P.CustomerID
    LEFT JOIN PolicyTypes PT ON P.PolicyTypeID = PT.PolicyTypeID
    LEFT JOIN Providers PR ON PR.ProviderID = PT.ProviderID
    WHERE PR.ProviderID <> 100001
)

Upvotes: 1

Related Questions