Clever Geek Handbook

Select records that are only related to a record in another table

Not sure if the title fully explains this scenario, so I'll be as descriptive as possible. I am using a SQL Server database and have the following 4 tables:

CLIENTS

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

POLITICS

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

SUPPLIERS

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

This is obviously a stripped down version and the actual database contains many more records. What I want to do is return a list of customers that ONLY have products from a specific vendor. So in the example above, if I want to return clients who have policies from 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

For now, it will return both customers to the Customers table. But client Mr. J Bloggs is actually pursuing the policies provided by Bloggs Plc. I do not want it. I only want to return clients who ONLY enforce policies from ABC Ltd, so the SQL I need should only return Mr J Smith.

Hope I was clear, if not please let me know.

Thank you very much in advance

Steve

+3


source to share


3 answers


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
)
+1


source


The idea is that you additionally perform NOT IN on clients 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
)
+1


source


try this ...

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)
+1


source






More articles:


All Articles