#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    1
    Rep Power
    0

    Sorting on fields from multiple records from the same table


    I have a "people" table which contains information about each person including id and surname fields. There is also a "forename" table, which contains a a single forename and the id of the people record whose forename it is, and a sequence number indicating which of their forenames it is.

    E.g. Rob Ben Smith and Jen Smith will be held as follows:

    people table:

    01 Smith ...
    02 Smith ...

    forename table

    Rob 01 01
    Ben 01 02
    Jen 02 01

    This allows each person to have unlimited forenames.

    How can I list people in alphabetical order on surname then forenames - can this be done with a single SQL statement? Presumably I'd have to concatenate each persons forenames into a single field.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    Why on earth would you do it that way?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by SimonJM
    Why on earth would you do it that way?
    because surnames and forenames aren't really what this design is about

    you heard it here first

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    Originally Posted by r937
    because surnames and forenames aren't really what this design is about

    you heard it here first

    What? You mean someone is being economical with the truth? Say it aint so!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo