SQL: query with complex subqueries

I have the following tables in my game's database:

rankedUp (image_id, user_id, created_at)
globalRank (image_id, rank )
matchups (user_id, image_id1, image_id2)

All image_ids in globalRank table are assigned a rank which is a float from 0 to 1

Assuming I have the current logged in user's "user_id" value, I'm looking for a query that will return a pair of image ids (imageid1, imageid2) such that:

1. imageid1 has lower rank than imageid2 but is also the next highest rank less than imageid2
2. matchups table doesn't have (userid,imageid1,imageid2) or (userid,imageid2,imageid1)
3. rankedup table doesn't have (userid,imageid1) or if it does, the createdat column is older than X hours

What I have so far for requirement 1 is this:

SELECT lowerImages.image_id AS lower_image, higherImages.image_id AS higher_image
FROM global_rank AS lowerImages, global_rank AS higherImages
WHERE lowerImages.rank < higherImages.rank
AND lowerImages.image_id = ( 
    S开发者_JAVA百科ELECT image_id
    FROM (
        SELECT image_id
        FROM global_rank
        WHERE rank < higherImages.rank
        ORDER BY rank DESC 
        LIMIT 1 , 1
        ) AS tmp 
    ) 

but it doesnt work because I can't reference higherImages.rank in the subquery.

Does anyone know how I could satisfy all of those requirements in one query?

Thanks for your help

EDIT:

I now have this query but I don't know about the efficiency and I need to test it for correctness:

SELECT lowerImages.image_id AS lower_image,
       max(higherImages.image_id) AS higher_image
FROM global_rank AS lowerImages, global_rank AS higherImages
WHERE  lowerImages.rank  <  higherImages.rank

AND 1  NOT IN (select 1 from ranked_up where
    lowerImages.image_id = ranked_up.image_id
    AND ranked_up.user_id = $user_id
    AND ranked_up.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY))

AND 1 NOT IN (
    SELECT 1 from matchups where user_id = $userId 
            AND lower_image_id = lowerImages.image_id 
            AND higher_image_id = higherImages.image_id
            UNION
            SELECT 1 from matchups where user_id = $user_id
            AND lower_image_id = higherImages.image_id 
            AND higher_image_id = lowerImages.image_id
)
GROUP BY 1

the "not in" statements I'm using are all indexed so they should run fast. The efficiency problem I have is the group by and selection of the global_rank tables

This question is a revision of Pretty Complex SQL Query, which should no longer be answered.

select
(
select image_id, rank from
rankedup inner join globalRank 
on rankedup.image_id = globalRank .image_id
where user_id = XXX 
limit 1, 1
) as highest,
(
select image_id, rank from
rankedup inner join globalRank 
on rankedup.image_id = globalRank .image_id
where user_id = XXX 
limit 2, 1
) as secondhighest

I normally use SQL Server, but this i think is the translation for mysql :)

This should do the trick:

SELECT lowerImages.*, higherImages.* 
FROM globalrank AS lowerImages, globalrank AS higherImages
WHERE lowerImages.rank < higherImages.rank
AND lowerImages.image_id = ( 
    SELECT image_id
    FROM (
        SELECT image_id
        FROM globalrank
        WHERE rank < higherImages.rank
        ORDER BY rank DESC 
        LIMIT 1,1
        ) AS tmp 
    ) 
AND NOT EXISTS (
    SELECT * FROM matchups 
    WHERE user_id = $user_id 
    AND ((image_id1 = lowerImages.image_id AND image_id2 = higherImages.image_id)
        OR (image_id2 = lowerImages.image_id AND image_id1 = higherImages.image_id))
)
AND higherImages.image_id NOT IN (
    SELECT image_id FROM rankedup 
    WHERE created_at < DATE_ADD(NOW(), INTERVAL 1 DAY)
    AND USER_ID <> $user_id
)
ORDER BY higherImages.rank

I'm assuming the PKs of matchups and rankedup include all columns in those tables. This would allow the second 2 sub-queries to utilize the PK indexes. You would probably want an ordered index on globalrank.rank to speed up the first sub-query.