Order by relational values?

Databases
1

I’m quite confident with basic MySQL queries, but this one has me stumped.

The problem: I am building a simple PM/Mail system where a user can sort their inbox by each heading column to quickly find what they are looking for. The user that the mail is To/From is stored in the database as a numerical value, the user ID. As the table is generated the full name of each user is fetched from another table via this ID. Obviously this presents a problem, when someone sorts their inbox by the To/From columns the user ID will be the sorting value, rather than the users name.

What I need to know: How can I order by the user names (which are stored in another table), instead of IDs? Is there some way to reference these values and associate them with the ID in the query?

Thanks.

2

You would join on the users table and order by the user name column of that table.

3

or you could use the username instead of a numeric id as the primary/foreign key

then you wouldn’t have to join

:slight_smile:

4

It’s a little more complicated… I’ll outline the table structure to show you what we’re working with. I’ve stripped it down to the columns we need to see and colour coded the relationships between columns / tables for clarity.

TABLE 1 - Mail
Columns: id, sender_type, sender_id

TABLE 2 - Sender Types
Columns: id

TABLE 3 - Contains full names
Can be any one of 3 database tables depending on sender type. Each contains the following consistent columns amongst others unique to that table.
Columns: id, first_name, last_name

I need to be able to order the list of mails fetched from table 1 by the combined first_name and last_name value of table 3… I’m out of my depth and would greatly appreciate a solution (code if possible) from someone that knows how to achieve this.