CODEWITHSUNDEEP
sqlsql-servert-sqlselect
ImmortalStrawberry
ImmortalStrawberry

Reputation: 6091

Query where any not null values also have a flag (bit) set

I've got a Customer table that has 4 possible phone numbers against each customer. Each phone number also has a flag.

I am trying to query to find customers where: If all of the phone numbers they have present ALSO have the flag set.

So, they need one or more phone numbers NOT NULL AND For each phone number that is not null, does it have the flag = 1

I think I can write a very long and convoluted query to get this, but is there a simple way I am missing?

CREATE TABLE #Listing (id INT,
Customer VARCHAR(50),
Phone1 VARCHAR(20) NULL,
Phone1Flagged BIT,
Phone2 VARCHAR(20) NULL,
Phone2Flagged BIT,
Phone3 VARCHAR(20) NULL,
Phone3Flagged BIT,
Phone4 VARCHAR(20) NULL,
Phone4Flagged BIT)

INSERT INTO #Listing VALUES(1, 'Dave', NULL, 0, NULL, 0, NULL, 0, NULL, 0)
INSERT INTO #Listing VALUES(2, 'Fred', '0712345678', 0, NULL, 0, NULL, 0, NULL, 0)
INSERT INTO #Listing VALUES(3, 'Bob', '0712121212', 1, '0123121212', 0, NULL, 0, NULL, 0)
INSERT INTO #Listing VALUES(4, 'Bill', NULL, 0, NULL, 0, '0799999999', 1, NULL, 0)
INSERT INTO #Listing VALUES(5, 'Bert', NULL, 0, NULL, 0, '0799999977', 0, '0799999988', 1)
INSERT INTO #Listing VALUES(6, 'Andy', '01212121212', 1, '0121212122', 1, '0799999977', 1, '0799999988', 1)

And query:

SELECT * FROM #Listing

SELECT
    *
FROM
    #Listing
WHERE
    (
        Phone1 IS NULL
        OR
            (
                Phone1 IS NOT NULL
                AND Phone1Flagged = 1
            )
    )
    AND
        (
            Phone2 IS NULL
            OR
                (
                    Phone2 IS NOT NULL
                    AND Phone2Flagged = 1
                )
        )
--etc. etc. etc.

DROP TABLE #Listing

"Hoped for" results

id  Customer    Phone1  Phone1Flagged   Phone2  Phone2Flagged   Phone3  Phone3Flagged   Phone4  Phone4Flagged
4   Bill        False       False   0799999999  True        False
6   Andy    01212121212 True    0121212122  True    0799999977  True    0799999988  True

Upvotes: 1

Views: 83

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

The description is a bit vague, but you could rewrite your conditions as:

SELECT l.*
FROM #Listing l
CROSS APPLY (SELECT MIN(CASE WHEN (p IS NOT NULL AND f = 1) 
                             OR p IS NULL THEN 1 ELSE 0 END) r, COUNT(p) AS c
             FROM (VALUES(Phone1, Phone1Flagged),
                         (Phone2, Phone2Flagged),
                         (Phone3, Phone3Flagged),
                         (Phone4, Phone4Flagged)) s(p,f)
            ) sub
WHERE r= 1 AND c > 0;

DBFiddle Demo

Output:

┌────┬──────────┬─────────────┬───────────────┬────────────┬───────────────┬────────────┬───────────────┬────────────┬───────────────┐
│ id │ Customer │   Phone1    │ Phone1Flagged │   Phone2   │ Phone2Flagged │   Phone3   │ Phone3Flagged │   Phone4   │ Phone4Flagged │
├────┼──────────┼─────────────┼───────────────┼────────────┼───────────────┼────────────┼───────────────┼────────────┼───────────────┤
│  4 │ Bill     │ null        │ False         │ null       │ False         │ 0799999999 │ True          │ null       │ False         │
│  6 │ Andy     │ 01212121212 │ True          │ 0121212122 │ True          │ 0799999977 │ True          │ 0799999988 │ True          │
└────┴──────────┴─────────────┴───────────────┴────────────┴───────────────┴────────────┴───────────────┴────────────┴───────────────┘

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your structure is fine. But the question is a bit hard to follow. This answer is based on:

So, they need one or more phone numbers NOT NULL AND For each phone number that is not null, does it have the flag = 1

For this, the logic is:

SELECT l.*
FROM #Listing l
WHERE (Phone1 IS NOT NULL AND Phone1Flagged = 1) OR
      (Phone2 IS NOT NULL AND Phone2Flagged = 1) OR
      (Phone3 IS NOT NULL AND Phone3Flagged = 1) OR
      (Phone4 IS NOT NULL AND Phone4Flagged = 1) ;

Upvotes: 0

Related Questions