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 <ddevie...@gmail.com> wrote: > On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector.v...@gmail.com> 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 you > can't know if all array values are matches. Which is different from > > > Is the first array contained by the second > > from the <@ operator, no? > I'm unfamiliar with these operators, so am I missing something? > Just trying to understand the logic here. Thanks, --DD >