CODEWITHSUNDEEP
sql-server
BFry
BFry

Reputation: 915

Compare when one field is empty but concatenation is to be compared

I have two tables.

Tbl1_Students

Name Class Section  
Joe   8      A  
Pin   8      B  
Tea   8      C  
Mia   9      A  
Ton   9      B  
Mon   10  
Tim   7      A

Tbl_Exclusion 

Class Section
7       A    
8       B    
9

The query should exclude all records where 

class= 7 and Section =A    and  
Class= 8 and Section =B    and   
Class= 9 (all the sections of 9)

I have written below query : 

SELECT * FROM TBL_STUDENTS WHERE   
CLASS + ISNULL(SECTION, '') NOT IN (  
SELECT Class + ISNULL(SECTION, '') FROM tbl_EXCLUSION)

But this wont work for case 3 where if alone class name is given then exclude all rows where class 9 is given ignoring section.

Upvotes: 1

Views: 32

Answers (2)

BFry
BFry

Reputation: 915

My query would run for 10,00,00,000 rows atleast. The solution GarethD gave was good, but I still felt below one was more clear to me. Query would be:

SELECT * 
FROM tbl_student s
WHERE 
(
    (
        s.class + ISNULL(s.section, '') NOT IN 
        (
            SELECT e.class + e.section 
            FROM tbl_exclusion e 
            WHERE e.section IS NOT NULL
        ) 
    )
    AND
    (
        s.class NOT IN 
        (  
            SELECT e.class 
            FROM tbl_exclusion 
            WHERE e.section IS NULL 
        )
    )
)

Upvotes: 0

GarethD
GarethD

Reputation: 69809

Use NOT EXISTS rather than NOT IN. It allows you to use more than one field for comparison when doing the exclusion:

SELECT  * 
FROM    tbl_Students AS s
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    tbl_Exclusion AS e
            WHERE   e.Class = s.Class
            AND     (e.Section IS NULL OR e.Section = s.Section)
        );

WORKING EXAMPLE

CREATE TABLE #tbl_Students (Name VARCHAR(10), Class INT, Section CHAR(1));
CREATE TABLE #tbl_Exclusion (Class INT, Section CHAR(1));

INSERT #tbl_Students (Name, Class, Section)
VALUES 
    ('Joe', 8, 'A'), ('Pin', 8, 'B'), ('Tea', 8, 'C'), ('Mia', 9, 'A'), 
    ('Ton', 9, 'B'), ('Mon', 10, NULL), ('Tim', 7, 'A ');

INSERT #tbl_Exclusion (Class, Section) 
VALUES (7, 'A'), (8, 'B'), (9, NULL);

SELECT  * 
FROM    #tbl_Students AS s
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    #tbl_Exclusion AS e
            WHERE   e.Class = s.Class
            AND     (e.Section IS NULL OR e.Section = s.Section)
        );

DROP TABLE #tbl_Students, #tbl_Exclusion;

RESULTS

Name | Class | Section
-----+-------+---------
Joe  |   8   |   A
Tea  |   8   |   C
Mon  |  10   |  NULL

Upvotes: 1

Related Questions