SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by relational values?

    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. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,146
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    You would join on the users table and order by the user name column of that table.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    or you could use the username instead of a numeric id as the primary/foreign key

    then you wouldn't have to join

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •