Pull MIN and MAX prices from database

Question

I need to select all (*) the rows from table1, table2, table3.. but I need to select the MIN and MAX price from table 2 within this INNER JOIN. I've read up on how to do this, but how do I do this within an INNER JOIN, and how do I display it in a PHP variable.

Initial Problem: How do I display the min and max values once I pull them.. (e.g $Result['MinPrice'], $Result['MaxPrice']).

Here's my query:

$Query = mysql_query("
   SELECT      *
   FROM        table1
   INNER JOIN  table2 ON table1.UserID = table2.UserID
   INNER JOIN  tables3 ON table2.DeviceID = table3.DeviceID
   WHERE       table2.DeviceID = '$GetDeviceID'
");

Here is the tables structure:

table1 = usersinfo

UserID   UserFirstName     UserLastName    UserDisplayName
1        John              Doe             John D.

table2 = listings

ListingID  UserID   DeviceID
11         1        2

table3 = devices

DeviceID
2

Tell us what the problem is. No point in trying to figure out the problem from the query...that will lead to a lot of different ideas and most likely none of them will match your true problem. ;) — Till Helge
How do I display the min and max values once I pull them.. (e.g $Result['MinPrice'], $Result['MaxPrice']). — user1661548
You don't obtain any maximum or minimum values with your query. — Till Helge

James James · Accepted Answer · 2013-02-26T13:36:54

If you really want to do what you're asking in this way you can use the query that is displayed below. This does, however, return a lot of duplicate rows if you have multiple rows returned when querying. Try it and see if it works.

$Query = mysql_query("
    SELECT table1.*, table2.*, table3.*,
           MIN(table2.price) as minny, 
           MAX(table2.price) as maxxy
    FROM   table1
    INNER JOIN table2 ON table1.UserID = table2.UserID
    INNER JOIN tables3 ON table1.DeviceID = table3.DeviceID
    WHERE  table1.DeviceID = '$Something'
    GROUP BY table2.ListingAskingPrice
");

Then get this value by doing $result['minny'] and $result['maxxy']

Pull MIN and MAX prices from database

1 Answers