CODEWITHSUNDEEP
databasesqlite
Alex
Alex

Reputation: 67732

Select count from referenced column

I have the following tables:

  CREATE TABLE ips(
    ip TEXT PRIMARY KEY,    
    blocked INTEGER(1) DEFAULT 0
  );
  
  CREATE TABLE acc(
    ip TEXT NOT NULL,
    time INTEGER(4) DEFAULT (CAST(strftime('%s','now') AS INT)),
    FOREIGN  KEY(ip) REFERENCES ips(ip) ON DELETE CASCADE  
  );

How can I get a list ips from the first table that have blocked = 1 and 0 associated rows in the "acc" tables?

SELECT ip FROM ips WHERE blocked = 1 AND ...

Upvotes: 1

Views: 56

Answers (2)

forpas
forpas

Reputation: 164164

You can use NOT EXISTS:

select i.* from ips i
where i.blocked = 1
and not exists (select 1 from acc a where a.ip = i.ip)

or if you want only the column ip use EXCEPT:

select ip from ips where blocked = 1
except
select ip from acc

Upvotes: 2

DingDing
DingDing

Reputation: 38

you can use left outer join.

example : select * from ips a left outer join acc b on a.ip =b.ip where a.blocked = 0 and b.ip is null

Upvotes: 2

Related Questions