I have two MySQL tables: MEMBERS and POSTS. I need to make a query that will have following meaning:
Select all USERs that have some (>0) records in POSTS table => From them Select a USER who has the biggest ADS value in MEMBERS table.
I tried following thing:
"SELECT * FROM posts WHERE user=(select user from members where ads=(select Max(ads) from members))'
But this has a bug, that when USER with Max value in MEMBERS, has no records in POSTS table - SQL returns empty result.
My guess is that I should tell MySQL to choose Max value not from all rows, but only from pre-defined, but I don't know how to do it and if it's possible.
Can someone help me?
SELECT m.*
FROM members AS m
WHERE EXISTS
( SELECT *
FROM posts AS p
WHERE p.member_id = m.member_id
)
ORDER BY ads DESC
LIMIT 1
Does this work?
select m.member_id, max(p.ads)
from members m
join posts p
on m.member_id = p.member_id
group by m.member_id
order by max(p.ads) desc
limit 1