i'd grateful if me this. have 2 simple tables , create (not simple) query. 1 of tables stores user data, 1 stores messages sent between users. these:
table1 userid, username 0, alice 1, bob 2, tom 3, jerry table2 messageid, senderid, recipientid, message 0, 3, 2, "hello tom, how you?" 1, 2, 3, "hello jerry" 2, 2, 0, "happy birthday alice, hugs, tom" 3, 3, 1, "bob, what's there?" i create contact list of given user. mean, ordered list includes names of users got messages given user or sent messages him/her. contact list of tom this:
alice jerry and bob's contact list include jerry only. , on.
i figured out maybe have use 2 select operations , union them. example:
(select senderid table2 recipientid=2) union (select recipientid table2 senderid=2) i have 3 questions.
how can use join here see names table1 instead of ids table2?
how can sort result names?
and, possible solve in other way?
thanks.
i haven't tested it, query should give tom's contact list.
select distinct t1.username table1 t1 join ( select senderid userid, recipientid contactid table2 union select recipientid userid, senderid contactid table2 ) t2 on t1.userid = t2.contactid t2.userid = 2 order t1.username
Comments
Post a Comment