mysql - matching groupmembers against another group and add its name to the first -

i need take members of each group in table_a , in table_b if there 1 or more groups exact same members if ad resulting groupnames result.

table_a holds groupmembers:

+-------+----------+--------+-------+ |uniqeid|objectname|property|value | +-------+----------+--------+-------+ |0 |thing1 |color |grey | +-------+----------+--------+-------+ |1 |thing1 |hardness|100 | +-------+----------+--------+-------+ |2 |thingy |sofness |80 | +-------+----------+--------+-------+ |3 |thingy |color |brown | +-------+----------+--------+-------+ |4 |thingy |emits |gas | +-------+----------+--------+-------+ |5 |item |exists |1 | +-------+----------+--------+-------+ continues endless data kinds of propertys , objectnames.

table_b basicaly same holds objecttypes groups in table_a

+-------+----------+---------+ |uniqeid|objecttype|property | +-------+----------+---------+ |0 |stone |color | +-------+----------+---------+ |1 |stone |hardness | +-------+----------+---------+ |2 |stuff |softness | +-------+----------+---------+ |3 |stuff |color | +-------+----------+---------+ |4 |stuff |emits | +-------+----------+---------+ continues truckloads of data.

now make view table out of that this: +----------+--------------------+------------+ |objectname|propertys |objecttype | +----------+--------------------+------------+ |thing1 |color,hardness |stone | +----------+--------------------+------------+ |thingy |softness,color,emits|stuff | +----------+--------------------+------------+ |item |exists |null | +----------+--------------------+------------+

for object become type exact propertys must there on both sides,duplicate propertys schould ignored.

this should done in mysql only.

i tryd matching 2 groupconcats against each other faild, , views dont subquerys.

greets

a straightforward way join on list of properties.

fortunately, mysql has group_concat() function can produce such list, , can made produce list of unique, consistently ordered elements suitable comparison:

select     objectname,     a.propertys,     group_concat(objecttype) objecttypes (select objectname,     group_concat(distinct property order property) propertys     table_a     group 1) left join (select objecttype,     group_concat(distinct property order property) propertys     table_b     group 1) b on a.propertys = b.propertys group 1, 2 

the left join gives null in last column if there's no match.