title: how to select from a mysql table according to values in a second table
body: <p>I have a table Email which contains emails, like:</p>
  
  <pre><code>EmailID DateStamp                Subject
  8       2014-04-03 10:56:10.000  Subject of the Email
  9       2014-02-25 08:52:10.000  Subject of the Email
  10      2014-04-13 12:22:25.000  Subject of the Email
  11      2014-02-15 11:18:16.000  Subject of the Email
  12      2014-03-20 09:26:04.000  Subject of the Email
  </code></pre>
  
  <p>then I have a second table EmailDirection that connects users id and email id, where DirectionID is a numeric code that means: 1 = FROM; 2 = TO; 3 = CC and 4 = BCC</p>
  
  <pre><code>EmailID ProfileID DirectionID
  10      72        1
  10      91        2
  10      58        2
  10      57        3
  10      24        3
  10      44        3
  </code></pre>
  
  <p>I am looking to build a unique table where I have only four columns:</p>
  
  <pre><code>emailID    datestamp               sender    receiver
  10         2014-04-13 12:22:25.000 72        91
  10         2014-04-13 12:22:25.000 72        58
  10         2014-04-13 12:22:25.000 72        57
  10         2014-04-13 12:22:25.000 72        24
  10         2014-04-13 12:22:25.000 72        44
  </code></pre>
  
  <p>where sender is the user that sent the email and receiver is a user that received the email, no matter if directly or as CC and BCC. My code so far is:</p>
  
  <pre><code> SELECT Email.EmailID as 'id', Email.DateStamp as 'timed', 
   EmailDirection.ProfileID as 'sender' where profileID in (Select profileid from EmailDirection where directionid = 1), 
   EmailDirection.ProfileID as 'receiver' where profileid in (select profileid from EmailDirection where directionid != 1)
  FROM Email, EmailDirection
  WHERE Email.EmailID = EmailDirection.EmailID
  </code></pre>
  
  <p>but I get an error code 1064... I understand my problem is to specify the directionId for the sender and the receiver in the select statement, but I am not able to solve this simple puzzle...</p>