Not equivalent to the use of NOT ARRAY and entirely possible I have misunderstood the requirement ...do you have some more test cases the non array solution does not work for
Regards
Hector Vass
07773 352559
On Mon, Nov 27, 2023 at 9:29 AM Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector.vass@xxxxxxxxx> wrote:Not sure you need to use array why not simple table joins, so a table with your criteria x y z t joined to stuff to give you candidates that do match, then left join with coalesce to add the 'd'select
--a.id,b.test_id,
coalesce(a.id,b.test_id) as finalresult
from test a
left join (
select
test_id
from stuff a
inner join (values ('x'),('y'),('z'),('t')) b (v) using(v)
group by 1
)b on(a.id=b.test_id);
Hi Hector. Hopefully this is not a stupid question...How is that equivalent from the `NOT ARRAY ... <@ ...` though?The inner-join-distinct above will return test_id's on any match, but youcan't know if all array values are matches. Which is different from> Is the first array contained by the secondfrom the <@ operator, no?I'm unfamiliar with these operators, so am I missing something?Just trying to understand the logic here. Thanks, --DD
