How to do a select within another select in mysql

Question

How to do a select within another select in mysql

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I need to get the smallest value of a column to use as a condition in where , but I do not 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

mysql select where

asked by anonymous 08.11.2018 / 17:35

2 answers

1

Would that be?

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
    );

08.11.2018 / 17:53

Social button Google and Facebook login round Union of vectors in the C language

score 1 · Accepted Answer

Does it 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. Left Join to do an integration of the image table with the owners table And the group to group repeating values like owner name and id to not display duplicate records.

Test there and let me know if it worked.

Hugs