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]

Re: how do you sort records?

In article <1cdca2a7.04022 51010.5fec515d@ posting.google. com>, Phil Powell wrote:[color=blue]
> 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?[/color]

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 ]
[ sugapablo@12jab ber.com <--jabber IM ]

[Richard]

Re: how do you sort records?

soazine@erols.c om (Phil Powell) wrote in
news:1cdca2a7.0 402251010.5fec5 15d@posting.goo gle.com:
[color=blue]
> 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
>[/color]

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]

Re: how do you sort records?

I noticed that Message-ID:
<1cdca2a7.04022 51010.5fec515d@ posting.google. com> from Phil Powell
contained the following:
[color=blue]
>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?[/color]

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[color=blue]
>
>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?[/color]

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]

Re: how do you sort records?

I noticed that Message-ID: <3lqp30ti4vd15h 49kduc6t6bi9jei mb2nt@4ax.com>
from Geoff Berrow contained the following:
[color=blue]
>SELECT * from person, department where
>person.departm ent_id=departme nt.department ORDER BY department,last _name[/color]

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/