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