Sorting by Joined Table Rows
Results 1 to 3 of 3

Thread: Sorting by Joined Table Rows

  1. #1
    Senior Member
    Join Date
    Jul 2007
    Posts
    181

    Sorting by Joined Table Rows

    Hi all

    I need help with a MySQL query more than anything I think so I hope I am not posting this incorrectly.

    I have a list of Hotels in a page and each Hotel has multiple Hotel Rooms associated to it. My Hotel list page needs to display the cheapest priced room such as:

    "Rooms available from: x.xx".

    Now, I obtain this price with a second query inside the loop of my main Hotel query as such:

    PHP Code:
    // main query loops through all Hotels and pulls out values such as $hotelid and shows the hotel details on the page
    $qry "SELECT hotelid, field2, field3 etc...";
    while (
    $row mysqli_fetch_array($link,$qry)) {
    $hotelid $row['hotelid'];

    // now a second query to get the minimum hotel room price for this Hotel
    $min     "SELECT MIN(CAST(sellcost AS DECIMAL(8,2))) AS price FROM tblhotelrooms WHERE hotelid = $hotelid LIMIT 1";
    $minres mysqli_query($link$min);

    Now this is working fine however I need to allow the user to be able to SORT the page by price ascending / descending and as the price is not physically assigned to the main Hotel query, I am not sure how I can do this.

    Does the second query need to be integrated with my first query somehow so I can then sort by it?

    If so, could anyone help in constructing that query structure?

    Many thanks for reading and any help offered.

    Doug

  2. #2
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,902
    My first thought is that maybe a sub-query would be the simplest way?
    PHP Code:
    // define $sortOrder based on user input, then...
    $qry "
    SELECT 
      hotelid,
      field2,
      field3. -- etc...
      (
        SELECT MIN(CAST(sellcost AS DECIMAL(8,2))) 
        FROM tblhotelrooms WHERE hotelid = 
    $hotelid LIMIT 1
      ) AS price
    FROM -- etc....
    ORDER BY price 
    $sortOrder
    "

    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,902
    Actually, I guess you could just do it with a JOIN:
    PHP Code:
    $qry "
    SELECT 
      t1.hotelid,
      t1.field2,
      t1.field3,
      MIN(CAST(t2.sellcost AS DECIMAL(8,2))) AS price 
        FROM tblhotelrooms WHERE hotelid = 
    $hotelid LIMIT 1
      ) AS price
    FROM first_table AS t1
    INNER JOIN tblhotelrooms AS t2 USING(hotelid)
    -- WHERE (whatever your conditions are for the main query
    ORDER BY price 
    $sortOrder
    "

    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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