title: SQL - Inner join to exclude rows
body: <p>I have the following tables in a laravel application:</p>
  
  <pre><code>basket
  ------
  id  PK
  user_id FK
  coupon_id FK
  
  basket_items
  ------------
  id PK
  basket_id FK
  product_id FK
  quantity 
  purchaseable_id   
  purchaseable_type -- e.g. App\Job, App\CV
  
  products
  --------
  id PK
  name  -- e.g. standard job listing, premium job listing, cv download
  price
  
  jobs
  ----
  id PK
  product_id FK
  title
  start_dt
  expiry_dt
  description
  
  cvs
  ------
  id PK
  name
  </code></pre>
  
  <p>The <code>purchaseable_id</code> and <code>purchaseable_type</code> in the <code>basket_items</code> table are polymorphic relationships. E.g. a user can add a job to their basket which is a premium product listing - the purchaseable_id in this case would be the id of the job from the jobs table and the purchaseable_type would be <code>App\Job</code>. Sample rows in the basket_items table may look like the following:</p>
  
  <pre><code>id | basket_id | product_id | quantity | purchaseable_id | purchaseable_type
  ----------------------------------------------------------------------------
  1  | 1         |  1         |   1      |     1           | App\Job   
  2  | 1         |  3         |   1      |     1           | App\CV
  </code></pre>
  
  <p>I want a to list all basket_items for a user but exclude items if the purchaseable_type is <code>App\Job</code> and the job has expired.</p>
  
  <p>I have tried the following but it excludes all rows if the job has expired i.e. it excludes items of every purchaseable_type </p>
  
  <pre><code>select t1.*, t2.* 
  from baskets t1
  inner join basket_items t2 on t2.basket_id = t1.id
  left join jobs t3 on t3.id = t2.purchaseable_id and t2.purchaseable_type = 'App\\Job' 
  where t1.user_id = 1 and t3.expiry_dt &gt; now()  
  </code></pre>