Having a table of properties of an object and a table of conditions to make an action, how to query-CodePudding

Let's suppose we have two tables (in this case in PostgreSQL). The first one represents a many to many relationship between objects and properties, for every object there will be a row for every property it has:

CREATE Table objects(
id serial,
object_name text,
property text  
);

The second table represents a many-to-many relationship between actions and properties that an object must have to take them.

CREATE Table actions(
id serial,
action_name text,
property text  
);

How to write a query that, given an object name, returns all the actions that that object can do?

For example, given table objects as:

id	object_name	property
1	object1	can_swim
2	object1	no_fear
3	object2	can_swim

and table actions

id	action_name	property
1	scuba_dive	can_swim
2	scuba_dive	no_fear
3	waterpolo	can_swim

If I want to know what action can object1 do I should get scuba_dive and waterpolo as answers, while object2 can only do waterpolo

CodePudding user response：

Try with your data and let me know:

   select object_name,action_name
from (  select object_name,string_agg(property,',') as objects_property
         from  ( select object_name,property 
                 from objects
                 order by object_name,property  desc 
                ) as tt1
         group by object_name 
     ) as t1 
inner join 
    (   select action_name,string_agg(property,',') as action_property
        from  ( select action_name,property
                from actions 
                order by action_name,property desc 
              )as tt2
        group by action_name
    ) as t2 on t1.objects_property LIKE CONCAT('%', t2.action_property, '%')
where object_name='object1' ;

Check Fiddle