Not In clause with two resources

Queries and Resources
#1

Currently trying to build a table filled with items that have been created as tickets, but filter out items that have already been created. The resources exist on different databases, so i can't just do this in mysql, its gotta be the json query.

first resource is fairly straight forward.
select * from resourceA

second resource is
select UIC from resourceB

the json query should look like the following in my mind:

select * 
from {{ formatDataAsArray(resourceA.data) }} as a
where a.UIC not in  ( {{ formatDataAsArray( resourceB.data ) }} )

This returns all items in resourceA without filtering the correct rows out.
Appreciate any help.

#2

Hello,

if your resourceB is just an array of non-object types (string, numeric), you can try this

SELECT UIC
FROM {{resourceA.value}} a
WHERE a.UIC NOT IN (SELECT _ FROM {{ resourceB.value }})

query json

Hope that'll help.

#3

resourceA is a mysql query, so is resourceB, so i'll rename them for clarity.

select * from {{ formatDataAsArray( queryA.data ) }} where UIC not in ( {{ queryB.dataArray['0'].UIC }} )

getting things to be an array seemed like a good thing to set my compass to so i ended up with this.

Still doesnt seem to be filtering those items out though.

#4

Can you try and replace
( {{ queryB.dataArray['0'].UIC }} )

with
(SELECT _ FROM {{ queryB.dataArray['0'].UIC }})

let me know if it doesn't work.

2 Likes
#5

Thank you i totally looked over the Select _ FROM

I had to change it to

(SELECT UIC FROM {{formatDataAsArray(queryB.data)}})

but it works, thanks for your help, and patience.

1 Like