The idea here is to join (full outer) the 2 tables and then read the result table to check the rules
LOAD * Inline
[Type, Name, Color, Country
Vege, Cucumber, Green, Spain
Vege, Tomato, Red, Spain
Fruit, Orange, Orange, Turkey
Fruit, Apple, Green, Italy
Vege, Potato, Yellow, Italy
Vege, Cucumber, Green, Italy];
LOAD * INLINE
[VNo, VType, VName, VColor, VCountry, VValue
1,*, *, *, Spain, 10
2,*,* , Red,* ,20
3,Fruit, *, *, *,30
4,*, Apple, *,Italy, 40
*, -1*((R1+R2+R3+R4)=-4) as R;
Type, Name, Color, Country,
(Type=VType or VType='*') as R1,
(Name=VName or VName='*') as R2,
(Color=VColor or VColor='*') as R3,
(Country=VCountry or VCountry='*') as R4,
DROP Table Vegs;
Type, Name, Color, Country, if(R,VNo) as VNo, if(R, VValue) as VValue
Where Type <> Peek('Type') or Name <> Peek('Name') or Color <> Peek('Color') or Country <> Peek('Country')
Order By Type, Name, Color, Country, R desc, VValue desc;
DROP Table Vegs2;
Thank you maxgro!
It's working solution, unfortunately I have to make it via joins.
Is it possible to make some kind of synthetic key to keep the Exc Table somehow linked?
I'd like to choose one item in the Vegs3 table and see only the rows with matched conditions.
Thank you in advance