[RESOLVED] join query - looking for match in a serialized array

perpetualshaun

I have two tables: groups and members.

In the members table, there is a field for the group, and a member can be part of more than one group. So, I am storing those number values as a serialized array.

So then when I'm displaying a table of the groups in the system, I'm trying to write a join query that will pall all the appropriate values from the groups table including the COUNT of the members in a given group. Here's what I though might work, but doesn't:

SELECT pp_groups.*,
COUNT(pp_members.member_id) AS member_count
FROM pp_groups
LEFT JOIN pp_members ON pp_members.member_group LIKE '%pp_groups.group_id%'
WHERE 1
GROUP BY pp_members.member_id
ORDER BY group_title ASC LIMIT 0,50

Any ideas?

Thanx,

Shaun

paulnaj

Hi perpetualshaun,

Your question is a little confusing. For one thing it seems that you have a many-to-many relationship between members and groups.

That is, a group can contain more than one member and a member can be part of more than one group.

To get a many-to-many deal, you need a third table:

pp_groupmembers(group_id, member_id)

Then your SQL would look like:

SELECT g.*, COUNT(m.member_id) AS member_count
FROM pp_groups AS g
LEFT JOIN pp_groupmembers AS m USING (member_id)
GROUP BY g.group_title
ORDER BY g.group_title ASC
LIMIT 0,50

Paul 🙂

PS. You might want to go back and edit your code to get rid of that long first line in the SQL. Makes the post really hard to read :queasy:

perpetualshaun

Sorry for the confusion. Looking at it again this morning, I see that I posted the wrong query anyway! Here's the query that I'm trying to execute:

SELECT pp_groups.*,
COUNT(pp_members.member_id) AS member_count
FROM pp_groups
LEFT JOIN pp_members ON pp_members.member_group LIKE '%pp_groups.group_id%'
WHERE 1
GROUP BY pp_members.member_id
ORDER BY group_title ASC LIMIT 0,50

The value in the member_group is a serialized array of values.

Any ideas?

tomhath

I wouldn't use the serialized array, a child table is much easier to work with.

But with your table design, the query should work if you group by the fields in the select list, something like this:

SELECT pp_groups.group_title,
pp_groups.field2,
pp_groups.field3
COUNT(pp_members.member_id) AS member_count
FROM pp_groups
LEFT JOIN pp_members ON pp_members.member_group LIKE '%pp_groups.group_id%'
WHERE 1
GROUP BY  pp_groups.group_title,
pp_groups.field2,
pp_groups.field3
ORDER BY group_title ASC LIMIT 0,50