CODEWITHSUNDEEP
mysqlsql
HorusCoding
HorusCoding

Reputation: 745

How to join / union multiple results from same table?

I've mysql tables that looks like :

user_messages

id | user_id | phone_number | message | direction | created_at

users

id | name

I want to 'group by' user_messages two times and UNION the result. Why I want to do this? because user_id sometimes has a valid user id (anything but '-1') then I group by it, if it has -1, then group by phone_number.

I also want to left join the result with users table to get the user name in case user_id is set to a valid user

I'm almost done with the query, but the problem is:
- I want the result to have the record that results from group by to be the latest one, which means, the biggest created_at value 

select * from (
(
    select *, count(*) as `total` from
      (select `user_id`, `message`, `created_at`, `phone_number`,`direction` from `users_messages` where `user_id` != -1  order by `created_at` desc)
    as `t1` group by `user_id`
)
union
(
    select *, count(*) as `total` from
        (select `user_id`, `message`, `created_at`, `phone_number`,`direction`  from `users_messages` where `user_id` = -1 order by `created_at` desc)
    as `t2` group by `phone_number`
)
) as `t3`
left join (select `id`,`name` from `users`) as `t4` on `t3`.`user_id` = `t4`.`id` order by `created_at` desc

What this gets me is the results not sorted by created_at DESC

Update: The query actually works in my local machine but not on the production server. In my local machine I have 5.5.42 - Source distribution and in server Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper ... What could be wrong?

In local machine it correctly returns me the max created_at but in server it returns the FIRST created for the grouped by record

Upvotes: 0

Views: 1792

Answers (2)

Uueerdo
Uueerdo

Reputation: 15951

Something like this should work:

SELECT s.`user_id`, um.`phone_number`, s.msgCount
   , um.`message`, um.`created_at`, um.`direction`
   , u.`name` AS userName
FROM (
    SELECT `user_id`, IF(`user_id` = -1, `phone_number`, '') AS altID, MAX(`created_at`) AS lastCreatedAt, COUNT(*) AS msgCount
    FROM `users_messages` 
    GROUP BY user_id, altID
) AS s
INNER JOIN `users_messages` AS um 
    ON s.user_id = um.user_id 
    AND s.altID = IF(um.`user_id` = -1, um.`phone_number`, '')
    AND s.lastCreatedAt = um.created_at
LEFT JOIN `users` AS u
    ON s.user_id = u.user_id
ORDER BY um.created_at DESC
;
  • The s subquery gets the summary information for each user and userless phone number; the summary information calculated includes the most recent created_at value for use in the following....
  • The join to um gets the row data for their last messages (by including the lastCreatedAt value from s in the join criteria)
  • The final join to users is used to get the user.name for the known users (and assumes there will be no -1 user, or that such a user would have an appropriate 'unknown' name.)

Upvotes: 1

Jeremy Real
Jeremy Real

Reputation: 766

Since you're grouping by user_id and phone_number, you can't keep message or direction. Add a max function for created_at in each subquery. I think this would work.

select * from (
(
    select user_id
          ,'' as phone_number
          ,max('created_at') as 'created_at'
          ,count(*) as `total` from
      (select `user_id`
             ,`created_at`
       from `users_messages` 
       where `user_id` != -1)
    as `t1` group by `user_id`
)
union
(
    select '' as user_id
          ,phone_number
          ,max('created at') as 'created_at'
          ,count(*) as `total` from
        (select `created_at`
               ,`phone_number'
         from `users_messages` 
         where `user_id` = -1)
    as `t2` group by `phone_number`
)
) as `t3`
left join (select `id`,`name` from `users`) as `t4` 
on `t3`.`user_id` = `t4`.`id` 
order by `created_at` desc

Upvotes: 1

Related Questions