Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi all,

I am trying to construct a sql query which will retrieve the group name
number of people in each group from the below table structure

- member_id
- member_name
- member_lastname
- member_number
- member_region

- group_id
- member_id

- group_id
- group_name
- group_description

All i have available to search on is the group_id. I'm having trouble
retrieving the group_name and the number of members it contains as they
are in separate tables..

Any help much appreciated,


Re: PHP SQL Query

Quoted text here. Click to load it

SELECT  group_name FROM sms_groups WHERE group_id='xxx'
SELECT COUNT(member_id) FROM sms_people_groups WHERE group_id='xxx'

These are the SQL statments and some work will be required to pass it into

Re: PHP SQL Query

Thanks for your answer.

Is there any way of doing this from a single SQL statement, perhaps
using some sort of JOIN?

Re: PHP SQL Query

Mick wrote:
Quoted text here. Click to load it

Yes. Why don't you ask in comp.databases.mysql, though? It's new!

select group_name, count(sms_people.member_id) as group_count
from sms_groups inner join sms_people_groups using group_id
     inner join sms_people using member_id
group by group_name

E. Dronkert

Re: PHP SQL Query

Will do in future..

Thanks for your response, much appreciated!

Re: PHP SQL Query

Domestos wrote:
Quoted text here. Click to load it

select a.group_name, count(b.member_id)
from sms_groups a ,sms_people_groups b
where a.group_id='xxx' and b.group_id=a.group_id
group by group_name

Quoted text here. Click to load it

Site Timeline