CODEWITHSUNDEEP
sqlsqlite
user6939809
user6939809

Reputation: 3

How to select exclusions based on criteria in a table

I'm using SQLITE3 3.14.2 and trying to do the following :

Given two tables IP and lookem:

drop table IP;
create table IP(IP TEXT,
                Name TEXT);
insert into IP values('10.5.6.7','Joe');
insert into IP values('10.6.6.7','Joe');
insert into IP values('10.2.3.4','Frank');
insert into IP values('10.3.5.7','David');
insert into IP values('10.34.56.78','David');
--select * from IP;

drop table lookem;
create table lookem(IP TEXT,
                    Name TEXT);
insert into lookem values('','Joe');    -- Exclude Joe for All IPs
insert into lookem values('','George');  -- Exclude George for all IPs
insert into lookem values('10.4.5.6','Frank');-- Excl Frank for 1 ip
insert into lookem values('10.34.56.78','David');-- Excl David on Specific IP
--select * from lookem;

select a.ip,a.name,b.name,b.ip from lookem a join ip b on 
   (b.name like case when a.name='' then '%' else a.name end) and
   (b.ip   like case when a.ip=''   then '%' else a.ip   end);

The above select correctly works for me showing all cases where the lookem table matches the IP Table on multiple criteria substituting wildcards for blank values.

|Joe|Joe|10.5.6.7
|Joe|Joe|10.6.6.7
10.34.56.78|David|David|10.34.56.78

However, what's the opposite? What I really need are the exclusions on the matches. In other words, a single query that would return :

10.3.5.7|David
10.2.3.4|Frank

As the sole results. Thank you in advance for your assistance.

Upvotes: 0

Views: 34

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Here is one way using NOT EXISTS

SELECT a.ip,
       a.NAME
FROM   IP a
WHERE  NOT EXISTS (SELECT 1
                   FROM   lookem b
                   WHERE  ( a.IP = b.ip
                             OR b.ip = '' )
                          AND a.NAME = b.NAME)

In case you want to select columns from using lookem table then use Left JOIN 

SELECT a.ip,
       a.NAME
FROM   IP a
       LEFT JOIN lookem b
              ON ( a.IP = b.ip
                    OR b.ip = '' )
                 AND a.NAME = b.NAME
WHERE  b.NAME IS NULL

Also your first query can re-written using EXISTS

SELECT a.ip,
       a.NAME
FROM   IP a
WHERE   EXISTS (SELECT 1
                   FROM   lookem b
                   WHERE  ( a.IP = b.ip
                             OR b.ip = '' )
                          AND a.NAME = b.NAME)

In case you want to select columns from using lookem table then use Inner JOIN 

SELECT a.ip,
       a.NAME
FROM   IP a
       JOIN lookem b
         ON ( a.IP = b.ip
               OR b.ip = '' )
            AND a.NAME = b.NAME

Upvotes: 1

Related Questions