How filter with join ?

[Abarny]

hi guys,
I want to filter my request where when logs{}.newStateId!=5 i recover the projects{}.id but this join isn't match and give all projects{}.id . I can't see projects{}.icma cause it takes the values on the first join and i would have all projects{}.icma by projects{}.id.

Can you tell me what wrong on my request :

index="gtav2_projects"    
|fields projects{}.id, projects{}.client.name,projects{}.icma
| stats list(projects{}.id) as projet by projects{}.client.name 
| join[search index="gtav2_projects" projects{}.icma="xxx"]
| join [search index="gta_projets" logs{}.newStateId!=5 
|fields logs{}.projectId 
| rename logs{}.projectId as 'projet' ]
|rename projects{}.client.name as Client|table Client, projet, projects{}.icma

Thanks you very much for your answers,

[somesoni2 · Accepted solution]

Give this a try

index="gtav2_projects"    [search index="gta_projets" logs{}.newStateId!=5 
 |fields logs{}.projectId ]
| stats list(projects{}.id) as projet by projects{}.client.name 
 | join[search index="gtav2_projects" projects{}.icma="xxx"]
|rename projects{}.client.name as Client|table Client, projet, projects{}.icma

[Abarny]

It's okay but i don't have projects{}.icma. I have just xxx and 1 ICMA = 1 projectId. Do you know how i can do that ?

[somesoni2]

So you want to exclude projects which has projects{}.icma="xxx" Or only include those?

[Abarny]

I want find all project about this Client. With projects{}.icma="xxx" I find Client, I recover all projectId about this Client and i would all projects{}.icma because each projectId has an icma.

Thanks you for your answer

[somesoni2]

Try this

index="gtav2_projects"    [search index="gta_projets" logs{}.newStateId!=5 
  |fields logs{}.projectId ]
  | join[search index="gtav2_projects" projects{}.icma="xxx"]
 | stats list(projects{}.id) as projet list(projects{}.icma) as icma by projects{}.client.name 
 |rename projects{}.client.name as Client|table Client, projet, projects{}.icma

[Abarny]

I'm so stupid !

Thanks Somesoni2