On Fri, 27 May 2022 at 18:19, Andrus <kobrule...@hot.ee> wrote: > Hi! > > > Product type table contains product types. Some ids may missing : > > create table artliik (liiginrlki char(3) primary key); > insert into artliik values('1'); > insert into artliik values('3'); > insert into artliik values('4'); > ... > insert into artliik values('999'); > > Property table contais comma separated list of types. > > create table strings ( id char(100) primary key, kirjeldLku chr(200) ); > insert into strings values ('item1', '1,4-5' ); > insert into strings values ('item2', '1,2,3,6-9,23-44,45' ); > > Type can specified as single integer, e.q 1,2,3 or as range like 6-9 or > 23-44 > List can contain both of them. > > > How to all properties for given type. > Query > > select id > from artliik > join strings on ','||trim(strings.kirjeldLku)||',' like > '%,'||trim(artliik.liiginrlki)||',%' > > returns date for single integer list only. > How to change join so that type ranges in list like 6-9 are also returned? > Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report. > > Postgres 13 is used. > > Posted also in > > > https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer > > Andrus. >
Hello, As Adrian said the best idea would be to redesign your data model. For example make a third "contains" table where every product/type relationship is one record. After that your problem would be trivial and your life much easier. However, this is a first class place. The customer wants the pain the customer gets the pain: Bad idea which solves the immediate problem 1: Write a function which unpacks your "1,2,3,6-9,4" string into an array '1','2','3','6','7','8','9','4' and use the string=ANY(array_of_strings) syntax. Bad idea which solves the immediate problem 2: Write a trigger which unpacks the the "1,2,3,6-9,4" form into "1,2,3,6,7,8,9,4" at insert/update time and then use the the string=ANY(array_of_strings) syntax. Regards, Sándor