468,257 Members | 1,428 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,257 developers. It's quick & easy.

PHP SQL Query

Hi all,

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

sms_people
- member_id
- member_name
- member_lastname
- member_number
- member_region

sms_people_groups
- group_id
- member_id

sms_groups
- 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,

Cheers,
Michael

Feb 13 '06 #1
5 1405

"Mick" <mi****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi all,

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

sms_people
- member_id
- member_name
- member_lastname
- member_number
- member_region

sms_people_groups
- group_id
- member_id

sms_groups
- 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,

Cheers,
Michael


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
PHP.
Feb 13 '06 #2
Thanks for your answer.

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

Feb 13 '06 #3
Mick wrote:
Is there any way of doing this from a single SQL statement, perhaps
using some sort of JOIN?


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
Feb 13 '06 #4
Will do in future..

Thanks for your response, much appreciated!

Feb 13 '06 #5
Domestos wrote:
"Mick" <mi****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi all,

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

sms_people
- member_id
- member_name
- member_lastname
- member_number
- member_region

sms_people_groups
- group_id
- member_id

sms_groups
- 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,

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


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


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

Feb 13 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.