SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to sort data with conditions?

    Hello

    I have the following two tables
    Code:
    create table `people` (
      `ID` int unsigned not null,
      `First_Name` varchar(255) not null,
      `Last_Name` varchar(255) not null,
      `Nickname` varchar(255) not null,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    create table `acquaintance` (
      `Acquaintance_ID` int unsigned not null,
      `Person_ID` int unsigned not null,
      `Is_Friend` tinyint not null,
      PRIMARY KEY (`Acquaintance_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    The people table stored information on people in the application.
    The acquaintance table stores information on people I met.
    The join between the two tables is done via the Person_ID attribute in the acquaintance table and the ID attribute in the people table.

    I would like to perform the following query:
    Get the info on the people I know, sorted by name (see below the definition of what constitutes a name).
    Here is the trick:
    If a certain person is a friend of mine, I would SQL to compare his first name and then last name to the other peope. If the person isn't a friend of mine, the comparison should be based on his nickname:

    Here is something I tried:
    Code:
    SELECT 
     people.*
    FROM
     people, acquaintance
    WHERE ID = Person_ID
    ORDER BY case when (Is_Friend = 1) then First_Name, Last_Name else Nickname;
    This isn't working
    Can someone help me with the correct syntax?

    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY case when Is_Friend = 1
    then concat(First_Name, Last_Name) else Nickname end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict jasongr's Avatar
    Join Date
    Jul 2004
    Location
    usa
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I managed to solve it differently.
    Please tell me what you think of my solution:

    Code:
    order by case when (Is_Friend = 1) then First_Name else Nickname end, case when (Is_Friend = 1) then Last_Name end

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that works too, eh

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


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
  •