title: How to query/search name of non-friends of a certain user from database?
body: <p>I have 2 tables in database: Users and Friend. I want to make a query that searches for users name that are not friends with a certain user using @Query. How should the query look like?</p>
  
  <p>I have created this query for it:</p>
  
  <pre><code>@Query("SELECT u.name FROM Users u WHERE u.user_id NOT IN
               (SELECT f.friend_id FROM Friend f WHERE f.user_id= ?1)
          AND u.name LIKE %?2% ", nativeQuery = true)
  List&lt;String&gt; searchingNonFriendsByUserId(int userId, String search)
  </code></pre>
  
  <p>But this doesn't give the right results that I want.</p>
  
  <p>Info/attributes about tables:</p>
  
  <p>Users(user_id PK, name, score)</p>
  
  <p>Friend(id PK, user_id, friend_id)</p>
  
  <p><strong>EXAMPLE FOR CLARIFICATION</strong>:</p>
  
  <p>USERS:
  <strong>(1, "test", 5), (2, "Tim", 10), (5, "Tom", 11), (4, "test2", 13), (6, "Tam", 0)</strong></p>
  
  <p>FRIENDS: <strong>(1, 1, 2) , (2, 1, 5) , (3, 1, 4) , (4, 2, 5) , (5, 2, 1)</strong></p>
  
  <p>I want names (specified by the LIKE clause) of the users that are not a friend of "Tim" (user_id = 2) for example. Here Tim is friends with "Tom" and "test". For example I type in the letter "T" between the % of the LIKE clause for user_id = 2, Then I want only "Tam" and "test2" to be queried. </p>