title: SELECT data from 1st table whether exists or not in 2nd table but with conditions
body: <p>I need some help from some <code>sql/postgres</code> guru's. What I'm trying to accomplish is I have a table for <code>tracks</code>, <code>genres</code> and <code>playlists</code>. I need to select from table <code>tracks</code> if it already exists in <code>track_playlists</code> or not exists. Which I have kind of done already. Here is current code.</p>
  
  <pre><code>SELECT tr.*, MAX(tp.played) as last FROM tracks as tr 
  LEFT JOIN track_genres as tg ON tr.trk_id = tg.trk_id 
  LEFT JOIN track_playlists as tp ON tr.trk_id = tp.trk_id 
  AND tp.mem_id=12345 AND tp.chn_id=9 
  AND tp.played &gt; EXTRACT(epoch from NOW() - INTERVAL '4 hours') 
  AND (tr.artist != tp.artist AND tp.played &gt; EXTRACT(epoch from NOW() - INTERVAL '1 hours'))
  WHERE tg.rotation=2 GROUP BY tr.trk_id ORDER BY last ASC LIMIT 6
  </code></pre>
  
  <p>Example scenario:</p>
  
  <p>Table <code>tracks</code> </p>
  
  <pre><code>trk_id | artist  | title
  -------------------------
  1      | artist1 | title1
  2      | artist1 | title2
  3      | artist2 | title1
  4      | artist3 | title1
  5      | artist4 | title1
  6      | artist5 | title1
  7      | artist1 | title3
  8      | artist6 | title1
  </code></pre>
  
  <p>Table <code>track_genres</code></p>
  
  <pre><code>gen_id | trk_id | rotation
  --------------------------
  1      | 1       | 9 
  2      | 2       | 9
  3      | 3       | 2
  4      | 4       | 3
  5      | 5       | 9
  6      | 6       | 2
  7      | 7       | 10
  8      | 8       | 9
  </code></pre>
  
  <p>Table <code>track_playlists</code></p>
  
  <pre><code>pla_id | mem_id  | trk_id | chn_id | artist  | title | played
  -------------------------------------------------------------
  </code></pre>
  
  <p>Here are the tables that I am dealing with. Currently when a user starts out the <code>track_playlist</code> for them is empty. When they select a genre it should populate this table but not all at once hence the <code>Limit</code> clause, and it needs to select songs based on whether there are songs already or not in this table for the user. If there aren't any songs then it selects the songs via select query I'm trying. As the songs play, when it goes to pull new songs it needs to see what songs are already there in <code>track_playlist</code>. If the song is already in the playlist table then it should skip it and only pull songs that are not in the playlist table yet for this user. Once <code>all</code> songs are finally in the playlist for the user, it just needs to check the <code>played</code> <code>timestamp</code> to see if the last time played for that song is greater than 4 hours and that it's the oldest track by <code>timestamp</code> (least recently played) and not an artist that has played in last 1 hour.</p>
  
  <p>I hope this is a bit more clear.</p>
  
  <p>I used this answer to help me with the query of empty or not.</p>
  
  <p><a href="https://stackoverflow.com/questions/29670680/sql-update-data-in-2nd-db-from-1st-db-only-where-table-exists">sql update data in 2nd db from 1st db only where table exists</a></p>