How to make a select inside another select in mysql

Asked

Viewed 2,541 times

0

I need to take the lowest 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 the 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 are 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

  • I couldn’t understand what you need. Can you explain better?

  • What do you expect this select returns?

  • i need it to bring which the smallest Ordering, for example (SELECT MIN(ordergin) FROM #__osrs_photos) then I will use this information where is the WHERE Ordering = "lowest Ordering"

  • I’m not sure if it’s a select within another, or would have another way

  • Okay, but you have two ordering minor, edit the question and place a table with the return you need

  • A ORDER BY ordering LIMIT 1 doesn’t solve?

  • I answered your question, take a look if that’s what you need. if it is not, try to put an example of how you would like the records to appear on the screen, so we understand better and can help you.

Show 2 more comments

2 answers

2


I wonder if it’ll 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 integrate the image table with the proprietary table And the group to group the values that repeat as owner name and id to not display duplicate records.

Try it on and let me know if it works.

Hugs

  • I think I just confused the order scheme you need. You can change Max by Min if it’s the reverse order.

  • 1

    exactly that, worked out, just with what you commented, is min and not max...rs, Valew!!!

  • Glad I could help. tmj brother hugs

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

  • gave this error: #1235 - This version of Mysql doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

  • @Leandromarzullo I made a change in Query, please check if it works

  • Do you really need sub-sql? Why don’t you sort by ordering the main query and limits the result to 1?

Browser other questions tagged mysql select where

You are not signed in. Login or sign up in order to post.