How To Select Cheapest Prices

February 23, 2022

Hello everyone I can’t figure this out. I have a mysql statement which includes INNER JOIN for two tables. I need to get 1000 cheapest prices ordered by minimum to maximum. I have looked up on the internet but I can’t seem to find the correct statement. The query is a prepared statement and I’ve changed the ? values in order to make it more readable. It works perfectly fine to get 1000 values from both table where conditions are true.The Code is below:

SELECT teachers.fname, 
       LEFT(teachers.lname, 1) AS lname, 
       profile.img_url, 
       profile.gender, 
       profile.title, 
       profile.introduction, 
       profile.city, 
       profile.preference, 
       profile.price,
       profile.keyword 
FROM profile 
INNER JOIN teachers ON profile.user_id = teachers.id 
WHERE profile.keyword LIKE 'mathematics' 
AND profile.gender = 'm' 
AND profile.price >= 0 
AND  profile.price <= 300  
LIMIT 1000

>Solution :

This should do the trick

SELECT teachers.fname, 
       LEFT(teachers.lname, 1) AS lname, 
       profile.img_url, 
       profile.gender, 
       profile.title, 
       profile.introduction, 
       profile.city, 
       profile.preference, 
       profile.price,
       profile.keyword 
FROM profile 
INNER JOIN teachers ON profile.user_id = teachers.id 
WHERE profile.keyword LIKE 'mathematics' 
AND profile.gender = 'm' 
AND profile.price >= 0 
AND  profile.price <= 300  
Order by profile.price
LIMIT 1000

You can also use BETWEEN for filtering price:

SELECT teachers.fname, 
       LEFT(teachers.lname, 1) AS lname, 
       profile.img_url, 
       profile.gender, 
       profile.title, 
       profile.introduction, 
       profile.city, 
       profile.preference, 
       profile.price,
       profile.keyword 
FROM profile 
INNER JOIN teachers ON profile.user_id = teachers.id 
WHERE profile.keyword LIKE 'mathematics' 
AND profile.gender = 'm' 
AND profile.price BETWEEN 0 AND 300 -- Equivelant to AND profile.price >= 0
                                    -- AND profile.price <= 300
Order by profile.price
LIMIT 1000