Re: How to eliminate extra "NOT EXISTS"-query here?

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(at)gmail(dot)com>
wrote:

> On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector(dot)vass(at)gmail(dot)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
>