DanDaBeginner Posted February 28, 2007 Share Posted February 28, 2007 I have this table: message --------+-----------+-------------+----------+-------+-----------+ msg_id + sender_id + recepient_id + content + read + time_sent + --------+-----------+-------------+----------+-------+-----------+ auto 1 2 hello! 0 4pm ---------the value of this incre- 2 1 hi! 0 5pm field is TIMEDATE ment 3 1 cute! 0 10am this is just a sample 1 100 how r u 0 5pm 4 1 lol! 0 6am 1 4 c u! 0 3am 5 1 hehe! 0 1am ------------------------------------------------------- SELECT sender_id as sid,MAX(message_id) as msg_id FROM dating_message WHERE recepient_id = 1 GROUP BY sender_id SELECT recepient_id as sid,MAX(message_id) as msg_id FROM dating_message WHERE sender_id = 1 GROUP BY recepient_id --------------------- how can I combine this two query? if I will use definitely they will not be combine because they have different msg_id.. for example the 1st query got an sid=2 msg_id=100 2nd query got an sid=2 msg_id=200 how can I get sid=2 msg_id=200 get the sid and the highest msg_id that has the the same sid in query1 and query2? Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/ Share on other sites More sharing options...
fenway Posted February 28, 2007 Share Posted February 28, 2007 You'll need to use the first query as a derived table, and join it to the 2nd query. Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196133 Share on other sites More sharing options...
DanDaBeginner Posted March 1, 2007 Author Share Posted March 1, 2007 thanx for the help fenway! how was that, an INNER JOIN? in that case how can I get the highest message_id if the first query is ID=1 msg_id=100 and the 2nd is ID=1 msg_id=400? Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196676 Share on other sites More sharing options...
DanDaBeginner Posted March 1, 2007 Author Share Posted March 1, 2007 could you give me an example code? for better understanding.. thanx.. I appreciate... Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196806 Share on other sites More sharing options...
DanDaBeginner Posted March 1, 2007 Author Share Posted March 1, 2007 I came up with the solution: is this the professional way? if you have a better idea please help: --- SELECT sid, MAX(msg_id) as msg_id FROM (SELECT sender_id as sid, MAX(message_id) as msg_id FROM dating_message WHERE recepient_id = 1 GROUP BY sid UNION SELECT recepient_id as sid, MAX(message_id) as msg_id FROM dating_message WHERE sender_id = 1 GROUP BY sid) sub GROUP BY sid ORDER BY msg_id DESC --- Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196849 Share on other sites More sharing options...
fenway Posted March 1, 2007 Share Posted March 1, 2007 That's probably good enough... the only other option would be to union the two tables first, without max() or group by, and then do the outer max/group by -- but I'm not sure that would be faster, because the union would return many more results, which have to go via a temporary table. Provided you've indexed the group by'ed columns, that should be fine -- post the EXPLAIN just to be sure. Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196859 Share on other sites More sharing options...
DanDaBeginner Posted March 1, 2007 Author Share Posted March 1, 2007 thanx again.. before I use the code UNION in it I already get their MAX(message_id) for the 1st and 2nd table so that in my main query it will lessen the msg_id that will be read to select the MAX(message_id).. Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196883 Share on other sites More sharing options...
fenway Posted March 1, 2007 Share Posted March 1, 2007 That was my point... glad you got it working. Quote Link to comment https://forums.phpfreaks.com/topic/40516-solved-query-problem/#findComment-196911 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.