Getting row data and a count - maybe simple

[Gregory.Spencer@lycos.co.uk]

Help,

I have a query in MySQL which gets the details of members of a club.

e.g. Select * from members.

however, in the same query I want to return the amount of "functions"
the member has attended.

therefore there is a "functions" table and because functions has a
many-many relationship with members I also have a "functionsl ink"
table.

To get the amount of functions that a particular member has attended
one would write a query like so:

select count(functionl ink_id) as cnt from functionslink where
functionlink.me mber_id=$MEMBER _ID

What I want to do is just have the one query that not only returns all
the member data in its returned row, but also the count of the number
of functions they attended.
At present using PHP code, I make the two calls separately to construct
this data in to a php array, but this involves many more calls to the
DB because for each row returned by the first query, I make another
call to the DB and something tells me this is a slow bad way of doing
things.

I am sure this is a simple common query but I cannot work it out.

Thanks in advance.

[jerry gitomer]

Re: Getting row data and a count - maybe simple

Gregory.Spencer @lycos.co.uk wrote:[color=blue]
> Help,
>
> I have a query in MySQL which gets the details of members of a club.
>
> e.g. Select * from members.
>
> however, in the same query I want to return the amount of "functions"
> the member has attended.
>
> therefore there is a "functions" table and because functions has a
> many-many relationship with members I also have a "functionsl ink"
> table.
>
> To get the amount of functions that a particular member has attended
> one would write a query like so:
>
> select count(functionl ink_id) as cnt from functionslink where
> functionlink.me mber_id=$MEMBER _ID
>
> What I want to do is just have the one query that not only returns all
> the member data in its returned row, but also the count of the number
> of functions they attended.
> At present using PHP code, I make the two calls separately to construct
> this data in to a php array, but this involves many more calls to the
> DB because for each row returned by the first query, I make another
> call to the DB and something tells me this is a slow bad way of doing
> things.
>
> I am sure this is a simple common query but I cannot work it out.
>
> Thanks in advance.
>[/color]

The following should work for you:

mysql> select m.membername, count(t.taskid)
-> from gOMembers m, gOTasks t
-> WHERE m.membername = t.membername
-> group by m.membername;

HTH
Jerry

[Gregory.Spencer@lycos.co.uk]

Re: Getting row data and a count - maybe simple

Thanks for that, it did help using the group by query in one case.
However, in another case the table linking is so complex (it seems
anyway) that I do not think it is possible to apply the above.
Therefore I have to have a second SQL call for each row returned from a
first SQL call to get a count of something.

Is this quite a common scenario and there may be cases where the first
result will return 500 rows and then for each of these returned rows
another SQL call is made to get a count to assoicate with each row.
By displaying this count in my results page I am adding a further 500
calls to the DB (within the same function mind you so only one
connection). Is this very bad?



For those interested the query is like this
Display information on users, but also display the number of events
that the user has registered for (but only count them if the event is
open).

It is the counting if the event is open part I found hard to achieve in
one SQL statement.

Thanks