title: Writing SQL Query to retrieve data via a Subquery?
body: <p>I need to write a query to retrieve values that respect the following condition,</p>
  
  <blockquote>
    <p>Select Users from UserTable that I am (me@gmail.com) not friends with</p>
  </blockquote>
  
  <p>Here are my table formats:</p>
  
  <p><a href="https://i.stack.imgur.com/5n4BU.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5n4BU.jpg" alt="enter image description here"></a></p>
  
  <p>My email is stated at UserID and my friends are stated in the FriendID column. I need to select the users from the UserTable that are not listed as friends in the FriendsTable. Seems like a simple query but I couldn't figure it out. Here's what I tried:</p>
  
  <p><strong>P.S:</strong> I just wrote this for clarification and therefore I <strong>don't</strong> want to write a parameterized query. I am not planning on distributing this.</p>
  
  <pre><code>SELECT * From UserTable  WHERE Email NOT LIKE '% (Select FriendsTable.FriendID From FriendsTable Where FriendsTable.UserID='me@gmail.com') %'  
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>jpw's query worked. However, how do I retrieve one random row from his solution's query?</p>
  
  <p>This doesn't work:</p>
  
  <pre><code>select TOP 1 * from UserTable where UserTable.Email &lt;&gt; '" + email + "' and Email not in (select case when FriendsTable.UserID = '" + email + "' then FriendsTable.FriendID else UserID end from FriendsTable where '" + email + "' in (UserID, FriendID)); ORDER BY NEWID()
  </code></pre>