how do you sort records?

[Phil Powell]

Very simplistic but I am utterly STUMPED at this one.

I have a db table "person" that I can sort by title, first_name,
last_name, or city with no problems at all because "title",
"first_name", "last_name", and "city" are columns in the table.

The requirement is to ALSO sort by department NAME. Problem is, I
can't do that in the "person" table because the column is
"department_id" which is a foreign key constraint to the department
table.

How do I sort the person records by department_name based on this?

I am using PHP 4.3.2 and MySQL 4.0.10

Now, second problem and even more of a problem:

The requirement is to ALSO sort by state NAME and country NAME. The
fields "state" and "country" in the "person" table contain only an
abbreviation of state and country; the corresponding full name of each
state and country are found in respective XML files in /xml/state.xml
and /xml/country.xml

So, NOW... how do I sort?

Thanx
Phil

[Sugapablo]

In article <1c**************************@posting.google.com >, Phil Powell wrote:

Very simplistic but I am utterly STUMPED at this one.

I have a db table "person" that I can sort by title, first_name,
last_name, or city with no problems at all because "title",
"first_name", "last_name", and "city" are columns in the table.

The requirement is to ALSO sort by department NAME. Problem is, I
can't do that in the "person" table because the column is
"department_id" which is a foreign key constraint to the department
table.

How do I sort the person records by department_name based on this?

SELECT tblPersons.*, tblDept.dept_name
FROM tblPersons INNER JOIN tblDept
ON tblPersons.department_id = tblDept.id
ORDER BY tblDept.dept_name, tblPersons.last_name, tblPersons.firstname;

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ su*******@12jabber.com <--jabber IM ]

[Richard]

so*****@erols.com (Phil Powell) wrote in
news:1c**************************@posting.google.c om:

Very simplistic but I am utterly STUMPED at this one.

I have a db table "person" that I can sort by title, first_name,
last_name, or city with no problems at all because "title",
"first_name", "last_name", and "city" are columns in the table.

The requirement is to ALSO sort by department NAME. Problem is, I
can't do that in the "person" table because the column is
"department_id" which is a foreign key constraint to the department
table.

How do I sort the person records by department_name based on this?

I am using PHP 4.3.2 and MySQL 4.0.10

Now, second problem and even more of a problem:

The requirement is to ALSO sort by state NAME and country NAME. The
fields "state" and "country" in the "person" table contain only an
abbreviation of state and country; the corresponding full name of each
state and country are found in respective XML files in /xml/state.xml
and /xml/country.xml

So, NOW... how do I sort?

Thanx
Phil

Hey Phil,

sorting your records is as simply as adding ORDER BY `tablename` ASC,
`tablename` DESC to your SQL Query!!

so the query would be like: SELECT * FROM `person` ORDER BY `state` ASC,
`country`ASC

shouldn't be that hard should it?

cheers,

--
Richard Tuin

http://www.richardtuin.nl

[Geoff Berrow]

I noticed that Message-ID:
<1c**************************@posting.google.com > from Phil Powell
contained the following:

I have a db table "person" that I can sort by title, first_name,
last_name, or city with no problems at all because "title",
"first_name", "last_name", and "city" are columns in the table.

The requirement is to ALSO sort by department NAME. Problem is, I
can't do that in the "person" table because the column is
"department_id" which is a foreign key constraint to the department
table.

How do I sort the person records by department_name based on this?
Do a join and add department name to the ORDER BY (unless I'm
misunderstanding something)

something like

SELECT * from person, department where
person.department_id=department.department ORDER BY department,last_name
I am using PHP 4.3.2 and MySQL 4.0.10

Now, second problem and even more of a problem:

The requirement is to ALSO sort by state NAME and country NAME. The
fields "state" and "country" in the "person" table contain only an
abbreviation of state and country; the corresponding full name of each
state and country are found in respective XML files in /xml/state.xml
and /xml/country.xml

So, NOW... how do I sort?

Add another table to your database to look up these values.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Geoff Berrow]

I noticed that Message-ID: <3l********************************@4ax.com>
from Geoff Berrow contained the following:

SELECT * from person, department where
person.department_id=department.department ORDER BY department,last_name

oops...

SELECT * from person, department WHERE
person.department_id=department.department_id ORDER BY
department,last_name
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/