mySQL Grouping

can anybody help me to find the right syntax?

[dwidmer]

Hello

I am trying to display a table with mySQL data. The table should be divided into multiple sections, each representing a region. Under each region there should be the staff members who are responsible for each region. I have a table that contains the staff member's names and their region in one row.

In order to display the region and it's staff members I tried: "Select * FROM staff GROUP BY Region ORDER BY Region ASC"

This only returns one staff member per region. Where's the mistake?

thx

Dan

[SinclairUser]

Try:

SELECT staff, region
FROM table WHERE
region=(SELECT distinct region
FROM table);

The subquery gives you a list of regions and the main query returns a list of staff and regions.

There may be a more elegant solution - I just put this down of the top of my head.

[dkubb]

Have you tried expanding your GROUP BY to include not only the Region but the Staff Member column as well?

eg: SELECT * FROM staff GROUP BY Region, StaffMember ORDER BY Region ASC

[SinclairUser]

dk - yep thats a better solution.

[killroy]

Group by X combines all rows where X is equal into one. That is NOT what you want. Waht you want is not doable in a single SQL query, unless you repeate the group for each pwerson and then do a simple sort. Try something like

SELECT staff,region ORDER BY region,staff;

But if you want summary information you have to do that seperately in a temp table, or afterwards in teh client app.

SN

[dwidmer]

in that case I really need to go with a temporary table. I need the result to look like this:

Region A
----------
Staff member 1
Staff member 2
Staff member 3

Region B
----------
Staff member 1
Staff member 2

etc.

But I have no idea how to set up a temporary table. I'm using PHP to retrieve/display the mysql results.

Dan