title: SQL query using LIKE and Union
body: <p>I'm trying to generate a list of friends for users to invite to an event, however only if they're not already attending the event or have a pending invite request. </p>
  
  <p>The way my friends system works, there are 3 columns in the table. userid1, userid2, and friendstatus. The user who sent the request goes into column 1(<code>userid1</code>), the user who received the request goes into column 2(<code>userid2</code>). Since the user creating the event could be in either column, I use the following query when adding friends to the event.</p>
  
  <pre><code>//this block is finding friends from column1
  SELECT userid1 as friendID 
  FROM friends 
  WHERE userid2 = $myUsername AND friendstatus = 1
  UNION 
  // this section is finding friends from column 2
  SELECT userid2 as friendId 
  FROM friends 
  WHERE userid1 = $myUsername AND friendstatus = 1
  </code></pre>
  
  <p>I set the userid as friendID because this query generates a list which I then use checkboxes for (which the value is determined from)</p>
  
  <p>However, once the event is created if users attending it want to add more friends, I need a way to pull their friends but only if those friends are not alread attending or have a pending request. 
  The column in my events table for users attending is called <code>acceptedInvites</code> and the column  for pending requests is in <code>inviteRequest</code>.</p>
  
  <p>So, here's the query that I can't get to work. If the <code>userid</code> is in the <code>acceptedInvites</code> or <code>inviteRequest</code> DO NOT select that value</p>
  
  <pre><code>//this section is finding friends from column1
  SELECT 
      events.inviteRequest, 
      events.acceptedInvites, 
      friends.userid1 as friendID 
  FROM friends 
  LEFT JOIN events on 
      friends.userid1 NOT LIKE '%events.inviteRequest%' AND 
      friends.userid1 NOT LIKE '%events.acceptedInvites%'
  WHERE friends.userid2 = $userid AND friends.friendstatus = 1 
  UNION 
  //this section is finding friends from column 2
  SELECT users.userid2 as friendId 
  FROM friends 
  LEFT JOIN events on 
      friends.userid2 NOT LIKE '%events.inviteRequest%' AND 
      friends.userid2 NOT LIKE '%events.acceptedInvites%'
  WHERE friends.userid1 = $userid AND friends.friendstatus = 1 
  </code></pre>
  
  <p>For example:</p>
  
  <p><strong>friends table</strong>
  columns are friend1, friend2 and friendstatus</p>
  
  <p>(row 1) userid1:1   userid2:userid2   friendstatus:1</p>
  
  <p>(row 2) userid1:3   userid2:1   friendstatus:1</p>
  
  <p>-userid1 is friends with userid1 and userid3. As I stated above, the user in userid1</p>
  
  <p><strong>events table</strong>
  columns used are acceptedInvites inviteRequests</p>
  
  <p>(row 1) acceptedInvites: "1~2"  inviteRequests : " "</p>
  
  <p>-userid1 and userid2 are attending the event, no pending invites</p>
  
  <p>Based on this, if userid1 wants to add more friends only userid3 will show up. </p>