Extract only data with certain value

Question

Sorry about the confusing title. Couldnt come up with better.

Well I have table messages with fields id, from, to, message

Lets say there are messages from John and Nate for Dave

so it would look like that

id|from|to|message
1|John|Dave|Hey    
2|Nate|Dave|Yooo    
3|John|Dave|Sup    
4|Nate|Dave|Waazza

Imagine there is more of them tho.

Ok I want to extract messages for Dave and I do it like that

SELECT * FROM messages WHERE ˙to` = 'Dave' ORDER BY id ASC

Ok all fine but this shows messages from both John and Nate.

Is there a way I could extract messages only from either(one) of them? Like I would extract only messages from John.

I am aware of doing another WHERE statement but I am looking for solution where I know only for who are messages not from who

Answer 1

Without knowing the from, you could use a sub query for example:

SELECT * 
    FROM messages 
   WHERE `to` = 'Dave' AND 
         `from` = (SELECT `from` 
                     FROM messages 
                    WHERE `to` = 'Dave'
                    LIMIT 1)
ORDER BY id ASC;

Using MIN:

SELECT * 
    FROM messages 
   WHERE `to` = 'Dave' AND 
         `from` = (SELECT MIN(`from`)
                     FROM messages 
                    WHERE `to` = 'Dave'
                    LIMIT 1)
ORDER BY id ASC;

Using MAX:

SELECT * 
    FROM messages 
   WHERE `to` = 'Dave' AND 
         `from` = (SELECT MAX(`from`)
                     FROM messages 
                    WHERE `to` = 'Dave'
                    LIMIT 1)
ORDER BY id ASC;

On the sub query you could use MAX / MIN functions to get different from.

Live DEMO of all the above queries.

NOTE: this is given you know the name of the 2 persons you want to find the messages.

You can use AND to get the data which matches names from to and from:

  SELECT * 
    FROM messages 
   WHERE `to` = 'Dave' AND
         `from` = 'John' 
ORDER BY id ASC;

Live DEMO.

Or you could use the below for all messages of both:

  SELECT * 
    FROM messages 
   WHERE (`to` = 'Dave' AND
         `from` = 'John') OR
         (`to` = 'John' AND
         `from` = 'Dave')
ORDER BY id ASC;

Live DEMO.

Answer 2

SELECT * FROM message WHERE `to` = 'Dave' 
AND `from` IN (SELECT MIN(`from`) FROM message WHERE `to` = 'Dave') ORDER BY id ASC; 

Is this what you are looking for ?

Demo

Answer 3

You're mixing ' (single quote) and ` (back tick) by the way. See Here

As to your question, you can easily add more requirements using AND.

SELECT * FROM messages WHERE `to`='Dave' AND `from`='John' ORDER BY id ASC

Now it shows only messages from John!