I am working on a groups directory page for a social network and I need some help with how to write some of the search filters.. so here is what I have for a table and what I need to do with it... There's no possible way of adding more fields or splitting it into more table it must be done with the current... Ok so its based off of wordpress so it has those famously annoying meta tables... so let me show you the table...
(not sure what sql or php version is but I know its one of the most up to date ones)
id (AUTO INCREMENT)
group_id
meta_key
meta_value

Here is an example of how I am using the table to make a groups hierarchy.
So for a group category called xbox 360 this is what is saved in the table..
(oh also the group id refers to another table that holds more info for a group(info that all groups have)
xbox 360 example

id 68
group_id 20
meta_key group_type
meta_value category

id 67
group_id 20
meta_key parent_cat <the parent categories id
meta_value -1 <because its a global group category

id 66
group_id 20
meta_key total_member_count
meta_value 26

id 65
group_id 20
meta_key last_activity
meta_value 2010-06-14 01:17:16

ok and a sub category for xbox 360 called call of duty 5 looks like this (ignore the same id values because it doesn't matter for an example)
id 68
group_id 23
meta_key group_type
meta_value category

id 67
group_id 23
meta_key parent_cat <the parent categories id
meta_value 20 <because its a global group category

id 66
group_id 23
meta_key total_member_count
meta_value 22

id 65
group_id 23
meta_key last_activity
meta_value 2010-06-14 01:17:16


So what I need to do is query based off of the correct parent_cat value and group_type because is category group can also have child user groups of type user_group

This is my current sql, but it isn't working....

PHP Code:
$sql "SELECT groupid FROM {$bp->groups->table_name_groupmeta}gm1, {$bp->groups->table_name_groupmeta} gm2, {$bp->groups->table_name_groupmeta} gm3 WHERE gm1.meta_key = 'parent_cat' AND gm1.meta_value = {$parentid} AND gm2.meta_key = 'group_type' AND gm2.meta_value = 'category' AND gm3.meta_key = 'last_activity' ORDER BY gm3.meta_value DESC" );