How to make a select inside another select in mysql

I need to take the smallest value of a column to use it as a condition in where, but I don't know how to do that. The column I need is ordering, as below: 

SELECT p.pro_name, p.id, f.image as foto
FROM #__osrs_properties p
LEFT JOIN (
    SELECT image, pro_id
    FROM #__osrs_photos
    WHERE ordering = "menor ordering"
) f ON f.pro_id = p.id
ORDER BY pro_name DESC
LIMIT 21

The tables look like this: 

osrs_properties

id|pro_name
1 | joao
2 | nome
3 | maria

osrs_photos

id|pro_id|    image    | ordering
1 | 1    | imagem1.jpg | 2
2 | 1    | imagem2.jpg | 1
3 | 2    | imagem3.jpg | 1
Author: Roberto de Campos, 2018-11-08
Source

2 answers

Does this solve what you need?

SELECT p.pro_name, p.id, f.image as foto, Max(f.ordering) AS orderingmax
FROM osrs_properties p
LEFT JOIN osrs_photos as f ON f.pro_id = p.id
GROUP BY p.pro_name, p.id
ORDER BY p.pro_name, orderingmax DESC
LIMIT 21

You use Max (F. ordering) to fetch the highest value items. The Left Join to make an integration of the image table with the owners table And group to group the values that repeat as owner name and ID so as not to display duplicate records.

Test ai and let me know if it worked out.

Hugs

 2
Author: Fernando VR, 2018-11-08 17:24:11

Would that be it?

SELECT 
    p.pro_name, 
    p.id, 
    f.image as foto
FROM 
    osrs_properties p
LEFT JOIN 
    osrs_photos f ON f.pro_id = p.id
AND f.pro_id in (
    SELECT MIN(pro_id) FROM osrs_photos ORDER BY ordering LIMIT 1
    );
 1
Author: Edgar Souza, 2018-11-08 17:18:21