Ben Ben - 3 months ago 7
MySQL Question

PHP/MySQL Have some rows in SELECT result have different values for a particular column when sorting

Right sorry for the complicated title wasn't sure how to best explain.

I have the following query:

SELECT * FROM uc_posts WHERE `postinguser` = 5 AND `id` NOT IN (SELECT dwable FROM uc_redwables WHERE redwabledby = 5)
UNION ALL
SELECT * FROM uc_posts WHERE id IN (SELECT dwable FROM uc_redwables WHERE redwabledby = 5)
ORDER BY time DESC LIMIT 20


The table
uc_posts
looks like this:

id | postinguser | time | message |
1 5 2016-08-08 23:50:45 *
2 5 2016-08-08 23:50:35 *
3 5 2016-08-08 23:50:25 *
4 5 2016-08-08 23:50:15 *


The table
uc_redwables
looks like this:

id | dwable | redwabledby | time |
1 3 5 2016-08-08 23:51:15


The output looks like this:

id | postinguser | time | message |
1 5 2016-08-08 23:50:45 *
2 5 2016-08-08 23:50:35 *
3 5 2016-08-08 23:50:25 *
4 5 2016-08-08 23:50:15 *


I'm trying to find a way to substitute the time in the selected row(s) from the
uc_redwables
table and sort the corresponding row with it (without actually updating that row in
uc_posts
).

So that it would look like this:

id | postinguser | time | message |
3 5 2016-08-08 23:51:15 * //time changed
1 5 2016-08-08 23:50:45 *
2 5 2016-08-08 23:50:35 *
4 5 2016-08-08 23:50:15 *


Can anyone help? Thanks!

Answer

Try this:

SELECT 
 p.`id` as id, 
 p.`postinguser` as postinguser,
 p.`message` as message,
 case r.dwable
  when p.id then r.`time`
  else p.`time`
  end as date_time 
FROM `uc_posts` p left join `uc_redwables` r on p.id = r.dwable
WHERE p.`postinguser` = 5 order by date_time desc;

This should produce an output like:

   id | postinguser | date_time                | message |
3        5            2016-08-08 23:51:15      *      
1        5            2016-08-08 23:50:45      *
2        5            2016-08-08 23:50:35      *
4        5            2016-08-08 23:50:15      *
Comments