Sorting by Joined Table Rows

condoug

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:

// 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

NogDog

My first thought is that maybe a sub-query would be the simplest way?

// 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
";

NogDog

Actually, I guess you could just do it with a JOIN:

$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
";