title: What I have done wrong with SELECT...JOIN?
body: <p>I have having to tables in my database.
  Table_A contains statistics of a trajectory segments (<code>segment_id, start/end coordinates, length</code>).</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE table_A(segment_id INT NOT NULL, session_id INT NOT NULL,
  segment_start_sec INT NOT NULL, lat_start DOUBLE PRECISION, lon_start DOUBLE PRECISION, 
  lat_end DOUBLE PRECISION, lon_end DOUBLE PRECISION, length DOUBLE PRECISION)
  </code></pre>
  <p>Table_B contains row <code>GPS</code>logs for all segments at <code>1 second</code> sampling rate.</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE table_B(session_id INT NOT NULL,seconds INT NOT NULL, 
     lat DOUBLE PRECISION, lon DOUBLE PRECISION)
  </code></pre>
  <p>Now I want select the <code>GPS</code> logs of each segment, from its start to finish. So I do the query:</p>
  <pre class="lang-sql prettyprint-override"><code> SELECT a.segment_id,
      b.seconds,
      b.lat,
      b.lon
   FROM table_A a
       LEFT JOIN table_B b 
          ON a.session_id = b.session_id
  </code></pre>
  <p><strong>The Problem:</strong></p>
  <p>This returns everything from start to finish of the longest segment.
  So:</p>
  <pre class="lang-sql prettyprint-override"><code>-- check segment 0 total records
  SELECT COUNT(*)
   FROM table_A a
       LEFT JOIN table_B b 
          ON a.session_id = b.session_id
  WHERE a.segment_id=0
  
  289
  </code></pre>
  <p>And</p>
  <pre class="lang-sql prettyprint-override"><code>-- check segment 12 total records
  SELECT COUNT(*)
   FROM table_A a
       LEFT JOIN table_B b 
          ON a.session_id = b.session_id
  WHERE a.segment_id=12
  
  289
  
  -- same as segment_id=16
  </code></pre>
  <p>Apparently this is the <code>GPS</code> logs of the longest segment, but all the segments ended having same number of rows.</p>
  <p>How do I filter the <code>GPS</code> logs of each segment, depending on its starting and finishing time in this case?</p>
  <p>I reproduce these tables (complete) in this <a href="https://dbfiddle.uk/?rdbms=postgres_10&amp;fiddle=6aae959acf6adc4fed5cfd3863bf959e" rel="nofollow noreferrer">dbfiddle</a> including all the queries stated above.</p>