title: Laravel - Mysql Where Not In join
body: <p>I have three tables i.e. <code>users</code>, <code>galleries</code>, <code>orders</code>. the structure is simple. both <code>galleries</code> and <code>orders</code> have a column named <code>client_id</code> acting as a foreign key for <code>users</code>
  I need a result set with the list of only those <code>users</code> that are present in <code>galleries</code> but not in <code>orders</code>.
  I have tried following but it gives me all the users that are present in <code>orders</code></p>
  
  <pre><code>$users  =   DB::table('galleries')
          -&gt;join('users', 'users.id', '=', 'galleries.client_id')
          -&gt;join('orders', 'orders.client_id', '=', 'users.id')
          -&gt;select('galleries.client_id', 'galleries.path', 'users.first_name', 'users.last_name', 'orders.order_number')
          -&gt;where('galleries.session_id', null)
          -&gt;where('galleries.is_video', '1')
          -&gt;where('galleries.is_thumb', '0')
          -&gt;get();
      dd($users);
  </code></pre>
  
  <p>The query produced by upper code is</p>
  
  <pre><code>    select
    `galleries`.`client_id`,
    `galleries`.`path`,
    `users`.`first_name`,
    `users`.`last_name`,
    users.id,
    `orders`.`order_number`
  from
    `galleries`
    inner join
    `users`
      on
        `users`.`id` = `galleries`.`client_id`
      inner join
      `orders`
        on
          `orders`.`client_id` = `users`.`id`
  where
    `galleries`.`session_id` is null
    and
    `galleries`.`is_video` = '1'
    and
    `galleries`.`is_thumb` = '0'
  </code></pre>