Help with SQL Query to Exclude Values

Question

Help with SQL Query to Exclude Values

CRMUK

SSC Veteran

Points: 221
More actions
September 26, 2019 at 1:36 pm

#3683639

I would be grateful if someone could help.
I have a simple table in SQL 2016
CREATE TABLE [dbo].[Members](
[memb_MembershipID] [int] IDENTITY(1,1) NOT NULL,
[memb_MemberID] [int] NULL,
[memb_type] [nvarchar](40) NULL,)
The data may be multiple records with the same memb_MemberID as they may have a handful of memberships. e.g.
I need to query the table and list all records that have a 'Tier 2' or a 'Tier 3' but exclude if they also have a 'Tier 1'
In the example above member 4000 has all 3, but as they have a Tier 1, the other 2 rows need to be excluded from the output.
Same as member 4001, they have a Tier 1 and a Tier 3, so I also need to exclude them from the output.
Thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Something like this?

SELECT *
FROM dbo.Members m
WHERE m.memb_type IN ('Tier 2', 'Tier 3')
      AND NOT EXISTS
(
    SELECT 1
    FROM dbo.Members m2
    WHERE m2.memb_MemberID = m.memb_MemberID
          AND m2.memb_type = 'Tier 1'
);

CRMUK SSC Veteran Points: 221 More actions · Answer 2

That looks perfect and seems to produce the right results.

Thanks Phil

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Try this as well.

WITH Partitioned AS (
SELECT
memb_MembershipID
,memb_MemberID
,memb_type
,MIN(memb_type) OVER (PARTITION BY memb_MemberID) AS MinMemb
,MAX(memb_type) OVER (PARTITION BY memb_MemberID) AS MaxMemb
FROM dbo.Members
)
SELECT 
memb_MembershipID
,memb_MemberID
,memb_type
FROM Partitioned
WHERE MinMemb <> 'Tier 1'
AND MaxMemb IN ('Tier 2','Tier 3');

Phil's solution is likely to involve more reads, whereas mine will have more sort operations, so either may perform better.

John

drew.allen SSC Guru Points: 77014 More actions · Answer 4

John Mitchell-245523 wrote:

Try this as well.
WITH Partitioned AS (
SELECT
memb_MembershipID
,memb_MemberID
,memb_type
,MIN(memb_type) OVER (PARTITION BY memb_MemberID) AS MinMemb
,MAX(memb_type) OVER (PARTITION BY memb_MemberID) AS MaxMemb
FROM dbo.Members
)
SELECT 
memb_MembershipID
,memb_MemberID
,memb_type
FROM Partitioned
WHERE MinMemb <> 'Tier 1'
AND MaxMemb IN ('Tier 2','Tier 3');
Phil's solution is likely to involve more reads, whereas mine will have more sort operations, so either may perform better.
John

You don't need the MaxMemb in your query. You can just filter on memb_MemberID instead. Also, you may not need any sort with the appropriate index (memb_MemberID, memb_type).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA