[SOLVED] MySQL sorting based on value from another table, if it exists.

[meman1188]

This is a follow up to a previous post but involves a somewhat different mysql question.  I am using a subquery to pull in a value from another table, and if it exists I want to sort using that value, if not, i want to sort using a value in the original table.  I realize this is confusing, so I think this is the best way to explain it:

 

SELECT `items` . *,

(

    SELECT `time`

    FROM `updates`

    WHERE `updates`.`id` = `items`.`id`

    ORDER BY `time` DESC

    LIMIT 1

) AS `title_time`, IF(`title_time` IS NOT NULL, `title_time`, `items`.`original`) as `sort_date`

FROM `items`

WHERE ( `items`.`group_id` IN ( 111, 112) )

ORDER BY `sort_date` DESC

LIMIT 9

 

Basically it is saying `title_time` is not defined, which i understand because the subquery needs to run on each row, but before that can happen it needs to sort the rows, using a value it hasn't pulled yet.  I'm just not sure how i can restructure this to accomplish what i'm looking for.

 

Note: there can be more than one row in update per item.id, but i always just want the latest one.  So i think this rules out using a join.

 

Thanks for the help!

[fenway]

Why the correlated subquery? Why not just join in a dervied table of max update times?

[meman1188]

Why the correlated subquery? Why not just join in a dervied table of max update times?

 

I would actually like to avoid the subquery if i can, but is there a way to have mysql create the derived table automatically (and update it), or is that something i would need to maintain separately from the original `update` table on the PHP side?

 

Also that gave me the idea of using views.  I'm kind of unfamiliar with the specifics, but they seem like something that might be able to do this.  Is that correct?

 

Thanks for the response

[fenway]

You can derive tables on the fly, yes

[meman1188]

You can derive tables on the fly, yes

 

Can you give an example of how I can set that up, or point me in the direction of a tutorial.  A quick google search didn't return anything promising.

[fenway]

Something like:

 

SELECT 
i.*
, u.time AS title_time
, IFNULL( u.time, i.original ) AS sort_date
FROM items AS i
LEFT JOIN 
(
    SELECT id, MAX(`time`) AS time
    FROM `updates`
    GROUP BY id
) AS u ON ( u.id = i.id )  
WHERE i.group_id` IN ( '111', '112' )
ORDER BY 
IF(`title_time` IS NOT NULL, `title_time`, `items`.`original`) as `sort_date`
FROM `items`
WHERE ( `items`.`group_id` IN ( 111, 112) )
ORDER BY IFNULL( u.time, i.original ) DESC
LIMIT 9 

[meman1188]

Works great, thanks for the help!