title: Sql select row if no row exists in other table
body: <p>I have a <code>users</code> table and a <code>likes</code> table. A user gets displayed a random other user's data and can decide whether he likes or dislikes it. I am struggling with selecting a new random user partner for the acting user that he has not yet rated!</p>
  
  <p>Now, I am trying to select all users that do not have a row in the <code>likes</code> table, with a relation rating of the acting user <code>user</code> to the rated user <code>partner</code>.</p>
  
  <p>The <code>users</code> table is a standard user table, in <code>likes</code> I have the columns, <code>id</code>, <code>user</code>, <code>partner</code> and <code>relation</code>.</p>
  
  <p>I am using Laravel Eloquent, but can also use raw sql.</p>
  
  <p>My attempt:</p>
  
  <pre><code>// $oUser-&gt;id is the acting user
  $oSearch = Db_User::
                select( 'db_users.*', 'db_likes.*' )
                -&gt;where( 'db_users.id', '&lt;&gt;', $oUser-&gt;id )
                -&gt;where( 'db_likes.user', '=', $oUser-&gt;id )
                -&gt;where( 'db_likes.relation', '&lt;&gt;', 'dislike' )
                -&gt;where( 'db_likes.relation', '&lt;&gt;', 'like' )
                -&gt;where( 'db_likes.relation', '&lt;&gt;', 'maybe' )
                -&gt;join( 'db_likes', 'db_users.id', '=', 'db_likes.partner' );
  </code></pre>
  
  <p>It is wrong, because I do not get any new user selected with this attempt. I think it is because there can not be found any row in <code>likes</code> ! There are no rows when he has not yet rated, so there is no result. Is this correct?</p>
  
  <p>EDIT:</p>
  
  <pre><code>$oSearch = Db_User::
                select( 'db_users.*' )
                -&gt;where( 'db_users.id', '&lt;&gt;', $oUser-&gt;id )
                -&gt;where( 'db_users.sex', '=', $strSex )
                -&gt;whereRaw( "not exists ( select 1 from db_likes where db_likes.relation in ('dislike','like','maybe') and db_likes.user = " .$oUser-&gt;id . " and db_likes.partner = db_users.id )" );
  </code></pre>
  
  <p>Error: <code>"{"error":{"type":"Illuminate\\Database\\QueryException","message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'IO8fMLYUPHfX1HrwkAWc2xqX' in 'where clause' (SQL: select</code>db_users<code>.* from</code>db_users<code>where</code>db_users<code>.</code>id<code>&lt;&gt; IO8fMLYUPHfX1HrwkAWc2xqX and</code>db_users<code>.</code>sex<code>= w and not exists ( select 1 from db_likes where db_likes.relation in ('dislike','like','maybe') and db_likes.user = IO8fMLYUPHfX1HrwkAWc2xqX and db_likes.partner = db_users.id ) order by RAND() limit 1)","file":"\/Applications\/MAMP\/htdocs\/adamundeva-server\/adamundeva\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php","line":625}}"</code></p>
  
  <p>**</p>
  
  <h2>FINAL SOLUTION:</h2>
  
  <p>**</p>
  
  <pre><code>$oSearch = Db_User::
                select( 'db_users.*' )
                -&gt;where( 'db_users.id', '&lt;&gt;', $oUser-&gt;id )
                -&gt;where( 'db_users.sex', '=', $strSex )
                -&gt;whereRaw( "not exists ( select 1 from db_likes where db_likes.relation in ('dislike','like','maybe') and db_likes.user = '" .$oUser-&gt;id . "' and db_likes.partner = db_users.id )" );
  </code></pre>