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_na me
FROM tblPersons INNER JOIN tblDept
ON tblPersons.depa rtment_id = tblDept.id
ORDER BY tblDept.dept_na me, tblPersons.last _name, tblPersons.firs tname;

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

[Richard]

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

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
misunderstandin g something)

something like

SELECT * from person, department where
person.departme nt_id=departmen t.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.departm ent_id=departme nt.department ORDER BY department,last _name

oops...

SELECT * from person, department WHERE
person.departme nt_id=departmen t.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/