.... AND NOT EXISTS IN (select 1 from ...)

[Jan Navratil]

Hi everyone,

i couldn't find answer how to build query with "EXISTS" or "NOT
EXISTS" in WHERE.

Is there way how to achieve it?

example:

select a.id, a.name, surname from users a
where a.status = 'Active'
and not exist (select 1 from bad_users b where b.id = a.id)

show me all users which are active and were not put to bad_users table
yet...

Thank you in advance,

Jan

[Honza Javorek]

Hello,

what approaches have you already tried without success?

Honza

[Jan Navratil]

I have tried something like this

$result = $db->table('input1 AS in1')
->select('*')
->where("started >= ? AND started < ?", $start,
$end)
->where(array("InputID" => 2))
->where(" not exists (select 1 from input2 AS in2
where in2.status = 0 and in2.started > in1.started)")
->where("status != ?", 0);

basically i want to use same table as subselect but then i get error
from mysql that my syntax is wrong

I guess you will kill me, because this is realated to NotORM which is
included in Nette framework (which probably differs and i should ask
in nette formu and not here)

Jan

[Jakub Vrana]

Hi,

You can use: ->where("NOT id", $db->bad_users())

Jakub