Return records that only match set criteria in various tables

Question

Post reply

Return records that only match set criteria in various tables

smw147

SSCommitted

Points: 1544
More actions
August 22, 2014 at 2:28 am

#290055

Hi all,
New on here so not sure if this is correct place.
I'm not sure if the title explains this scenario in full, so I will be as descriptive as I can. I'm using a Microsoft SQL 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
Regards
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

CREATE TABLE #CUSTOMERS (CustomerID INT,CustomerName VARCHAR(20))

INSERT INTO #CUSTOMERS (CustomerID,CustomerName) VALUES

(100001,'Mr J Bloggs'),

(100002,'Mr J Smith')

CREATE TABLE #POLICIES (PolicyID INT,PolicyTypeID INT,CustomerID INT)

INSERT INTO #POLICIES (PolicyID,PolicyTypeID,CustomerID) VALUES

(100001,100001,100001),

(100002,100002,100001),

(100003,100003,100001),

(100004,100001,100002),

(100005,100002,100002)

CREATE TABLE #POLICYTYPES (PolicyTypeID INT,PolTypeName VARCHAR(20),ProviderID INT)

INSERT INTO #POLICYTYPES (PolicyTypeID,PolTypeName,ProviderID) VALUES

(100001,'ISA',100001),

(100002,'Pension',100001),

(100003,'ISA',100002)--

CREATE TABLE #PROVIDERS (ProviderID INT,ProviderName VARCHAR(20))

INSERT INTO #PROVIDERS (ProviderID,ProviderName) VALUES

(100001,'ABC Ltd'),

(100002,'Bloggs Plc')

SELECT C.CustomerName, P.PolicyID, PT.PolTypeName, PR.ProviderName

FROM #Customers C

INNER JOIN #Policies P

ON C.CustomerID = P.CustomerID

INNER JOIN #PolicyTypes PT

ON P.PolicyTypeID = PT.PolicyTypeID

INNER JOIN #Providers PR

ON PR.ProviderID = PT.ProviderID

WHERE PR.ProviderID = 100001 -- turns outer join into inner join

AND NOT EXISTS (

SELECT 1

FROM #Policies Pli

INNER JOIN #PolicyTypes PTi

ON PTi.PolicyTypeID = Pli.PolicyTypeID

WHERE Pli.CustomerID = C.CustomerID -- outer reference

AND PTi.ProviderID <> 100001

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 2

Steve,

Look at Chris's code carefully, because he makes a point I think you missed. OUTER JOINs don't work as OUTER JOINs when you explicity reference the columns in the WHERE clause. They become INNER JOINs by default.

There is only one exception to this rule, when you reference the OUTER JOINed table to verify that a specific column is NULL such as in below code.

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 IS NULL

So if you're going to default your OUTER JOINs to INNER JOINs by using "WHERE PR.ProviderID = 100001", you might as well just bite the bullet and change them all to INNER JOINs.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

smw147 SSCommitted Points: 1544 More actions · Answer 3

Guys, this is perfect. Thanks for your help :-):-)

Regards

Steve