CODEWITHSUNDEEP
mysql
Fuxi
Fuxi

Reputation: 329

looking for tricky sql query solution

i'm having the following database structure (for messaging):

id   from_userid   to_userid   time_stamp   message

let's say i'm user with id 1 and i want to get a list of ALL user_ids i've been interacting with, sorted by timestamp - any idea how to do it?

thanks

Upvotes: 2

Views: 754

Answers (4)

mzedeler
mzedeler

Reputation: 4369

Something like this, perhaps?

 SELECT *
   FROM (
     SELECT from_id AS id, time_stamp
       FROM <table>
      WHERE to_id=<user id>
      UNION
     SELECT to_id AS id, time_stamp
       FROM <table>
      WHERE from_id=<user id>
   ) AS t
ORDER BY time_stamp

Upvotes: 3

Don
Don

Reputation: 4167

Since all of the other ways use more than one SELECT here's one using CASE

SELECT CASE
    WHEN to_userid=1 THEN from_userid
    ELSE to_userid
FROM table WHERE to_userid=1 OR from_userid=1 ORDER BY time_stamp

Upvotes: 0

naivists
naivists

Reputation: 33531

I would do it like this:

  • select all values + timestamps where "me" is from_userid
  • select all values + timestamps where "me" is to_userid
  • in both selects assign the same name to the "other" user id
  • join the result sets using UNION ALL
  • then order the result by the timestamp column
  • group by user id and min(timestamp)

In sql it would be something like this:

select rel_user, min(time_stamp) as first_contact from 
  ( 
    select time_stamp, to_userid as rel_user where from_userid=my_ID
     union all 
    select time_stamp, from_userid as rel_user where to_userid=my_ID
  )
 group by rel_user
 order by min(time_stamp)

Upvotes: 1

Luc M
Luc M

Reputation: 17324

SELECT *
FROM your_table
WHERE from_userid = 1 OR to_userid = 1
ORDER by time_stamp

Upvotes: 1

Related Questions