[SOLVED] query problem..

[DanDaBeginner]

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?

[fenway]

You'll need to use the first query as a derived table, and join it to the 2nd query.

[DanDaBeginner]

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?

[DanDaBeginner]

could you give me an example code? for better understanding.. thanx.. I appreciate...

[DanDaBeginner]

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

---

[fenway]

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.

[DanDaBeginner]

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)..

 

[fenway]

That was my point... glad you got it working.