What is the correct search syntax for my join NOT IN subsearch?

[senthil_cbe]

Hi

I am trying something like this :

select t1.field1
from
table1 t1
where
t1.id not in (select t2.id from table1 t2 where t2.id = t1.id and t2.field3 > 5)

I am trying to write this as :

index=table1 JOIN id NOT [search index=table1 field3 > 5] | TABLE field1d

Is this correct syntax?

[somesoni2 · Accepted solution]

Try this

index=table1 NOT [ search  index=table1 field3>5 | stats count by id |table id] | table id

OR

index=table1 | eval flag=if (field3>5,1,0) | stats sum(flag) as flag by id | where flag=0 |table id

[senthil_cbe]

I dont think it will work since I am doing NOT IN with subsearch resultset

select id from table1 where api=D and id not in (select id from table where err = 12)

[davebrooking]

Try something like

index=table1 | eventstats count(err) as allerrs by id | where api="D" | join id [search index=table1 err!=12 | stats count as not12s by id] | where allerrs=not12s

[somesoni2]

Try this

index=table1 api=D [search  index = table1 err=12 | stats count by id |table id] | table id

[senthil_cbe]

Quick clarification :

suppose I have a following records

Table 1

id err api
1 00 D
1 11 C
1 15 B
2 10 C
3 00 D
3 12 F

Find out id's who didn't encounter err=12 but have api=D
select id from table1 where api=D and id not in (select id from table where err = 12)

My O/P will be
id = 1

How can I achieve this?