I have a messages table that has a unique identifier `id` and also a column for `reply_to` which may contain the unique identifier `id`, but a default of NULL

So what happens is someone sends me a message, we each get a copy for the receiver and sender. If I reply, we each get another copy but the reply_to is filled out with the original message id. A single message can have multiple replies back and forth.

Say for a specific user I want to get all the messages they received, and if any of them have been replied to it only grabs the newest one.

I have two queries so far, assuming the user id is 1...

This query will grab the latest reply to a specific message for all messages that have been replied to for the user:

SELECT * FROM messages M1
WHERE M1.owner = 1 AND M1.reply_to IN 
      (SELECT M2.id FROM messages M2 ORDER BY M2.created DESC) 
GROUP BY M1.reply_to ORDER BY M1.created DESC

So if a message has 4 replies, it will only get teh most recent reply

Now this query will grab all the messages that don't have replies:

SELECT * FROM messages M1 
  WHERE M1.owner = 1 AND M1.reply_to IS NULL AND M1.id NOT IN 
         (SELECT M2.reply_to FROM messages M2 WHERE M2.reply_to IS NOT NULL)

both queries seem to work with what I'm trying to obtain. Even say for example, I get sent a message, and send a reply, and receive a reply on top of that. Then I get sent another message from teh same user for the same listing, but it wasn't a reply. The one query will still get the most recent reply, and the second query will pick up the one that didn't have a reply with all the same sources.

How can I combine these two queries into a list of message in descending date order?

Do I use JOIN or something else? How expensive is this? Is there a better more intuitive way to accomplish my goal?

Thanks for any help.

Just to let everyone know, I accomplished it another way. I changed my queries a little bit, and just executed both, put them in an array, and merged them. Problem solved.

Thanks to those of you who took a look.

I did not saw this before.
You solved it, but in a way that is inefficient.
The db is particulary well armed to handle this.

What you need is to union your 2 queries in a subquery, and do the ordering in the outer query.
Like this:

SELECT * 
FROM  (
    SELECT * FROM messages M1
    WHERE M1.owner = 1 AND M1.reply_to IN 
          (SELECT M2.id FROM messages M2 ORDER BY M2.created DESC) 
    GROUP BY M1.reply_to 
    UNION
    SELECT * FROM messages M1 
      WHERE M1.owner = 1 AND M1.reply_to IS NULL AND M1.id NOT IN 
             (SELECT M2.reply_to FROM messages M2 WHERE M2.reply_to IS NOT NULL)
) AS out
ORDER BY out.reply_to DESC

Thanks for the input. Here is what I have using your theory with my new queries:

select * from (SELECT *    FROM messages M1
    WHERE M1.to = 1 AND M1.to_deleted = 0 AND 
      M1.reply_to IS NOT NULL AND M1.ltype=0
    GROUP BY M1.reply_to 
UNION
SELECT * FROM messages M2 
   WHERE M2.ltype = 0 AND M2.to = 1 AND M2.to_deleted = 0 AND 
     M2.reply_to IS NULL AND M2.id 
     NOT IN
    (SELECT DISTINCT(M1.reply_to) 
        FROM messages M1 WHERE 
            M1.to = 1 AND M1.reply_to IS NOT NULL 
            AND M1.to_deleted = 0 AND M1.ltype=0) 
AS test ORDER BY test.reply_to DESC

I get the error

1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax 
to use near 'AS test ORDER BY test.reply_to DESC' at line 5

Any idea of what's going on?
thanks,
kb

you are missing an ")".
Just before "AS test".