title: How to get last N records after JOIN in SQL?
body: <p>I have three tables in mysql -<br />
  Table 1: <strong>notes</strong></p>
  <pre><code>+---------------+-------------+------+-----+---------+----------------+
  | Field         | Type        | Null | Key | Default | Extra          |
  +---------------+-------------+------+-----+---------+----------------+
  | id            | int         | NO   | PRI | NULL    | auto_increment |
  | user_id       | int         | NO   | MUL | NULL    |                |
  | note_id       | varchar(25) | NO   | UNI | NULL    |                |
  | topic         | text        | NO   |     | NULL    |                |
  | note_html     | text        | NO   |     | NULL    |                |
  | note_markdown | text        | NO   |     | NULL    |                |
  | access_type   | varchar(25) | NO   |     | NULL    |                |
  | date          | date        | NO   |     | NULL    |                |
  | time          | time        | NO   |     | NULL    |                |
  +---------------+-------------+------+-----+---------+----------------+
  </code></pre>
  <p>Table 2: <strong>notes_subject</strong></p>
  <pre><code>+--------------+-------------+------+-----+---------+----------------+
  | Field        | Type        | Null | Key | Default | Extra          |
  +--------------+-------------+------+-----+---------+----------------+
  | id           | int         | NO   | PRI | NULL    | auto_increment |
  | user_id      | int         | NO   |     | NULL    |                |
  | note_id      | varchar(25) | NO   | MUL | NULL    |                |
  | subject_name | text        | NO   |     | NULL    |                |
  +--------------+-------------+------+-----+---------+----------------+
  </code></pre>
  <p>Table 3: <strong>notes_tag</strong></p>
  <pre><code>+----------+-------------+------+-----+---------+----------------+
  | Field    | Type        | Null | Key | Default | Extra          |
  +----------+-------------+------+-----+---------+----------------+
  | id       | int         | NO   | PRI | NULL    | auto_increment |
  | user_id  | int         | NO   |     | NULL    |                |
  | note_id  | varchar(25) | NO   | MUL | NULL    |                |
  | tag_name | text        | NO   |     | NULL    |                |
  +----------+-------------+------+-----+---------+----------------+
  </code></pre>
  <p>and suppose the content stored in <strong>notes</strong> table as (I am leaving some attributes of this tables like note_html, note_markdown because it has large data) -</p>
  <pre><code>+----+---------+--------------+------------+----------+
  | id | user_id | note_id      | date       | time     |
  +----+---------+--------------+------------+----------+
  |  1 |       2 | nVf0It70bnjQ | 2020-08-08 | 11:18:17 |
  |  3 |       2 | RfYg3u59Ytup | 2020-08-06 | 12:50:51 |
  |  6 |       1 | e657ZxsYaWR2 | 2020-08-06 | 10:43:08 |
  |  7 |       2 | ARJuTcItPgbn | 2020-08-10 | 11:19:11 |
  |  8 |       2 | BW2aMLYN8CIF | 2020-08-10 | 11:24:35 |
  +----+---------+--------------+------------+----------+
  
  </code></pre>
  <p>and suppose the content stored in <strong>notes_subject</strong> table as -</p>
  <pre><code>+----+---------+--------------+--------------+
  | id | user_id | note_id      | subject_name |
  +----+---------+--------------+--------------+
  |  1 |       2 | nVf0It70bnjQ | computer     |
  |  3 |       2 | RfYg3u59Ytup | SQL          |
  |  6 |       1 | e657ZxsYaWR2 | computer     |
  |  7 |       2 | ARJuTcItPgbn | CN           |
  |  8 |       2 | BW2aMLYN8CIF | DLD          |
  +----+---------+--------------+--------------+
  </code></pre>
  <p>and suppose the content stored in <strong>notes_tag</strong> table as -</p>
  <pre><code>+-----+---------+--------------+--------------+
  | id  | user_id | note_id      | tag_name     |
  +-----+---------+--------------+--------------+
  |  7  |       1 | e657ZxsYaWR2 |  definition  |
  |  70 |       2 | nVf0It70bnjQ | computer fan |
  |  71 |       2 | nVf0It70bnjQ |  clean       |
  | 250 |       2 | RfYg3u59Ytup | sql          |
  | 251 |       2 | RfYg3u59Ytup | mysql        |
  | 252 |       2 | RfYg3u59Ytup |  select      |
  | 253 |       2 | RfYg3u59Ytup |  ASC         |
  | 254 |       2 | RfYg3u59Ytup |  DESC        |
  | 257 |       2 | ARJuTcItPgbn | cn           |
  | 258 |       2 | ARJuTcItPgbn |  IPV4        |
  | 261 |       2 | BW2aMLYN8CIF | AND          |
  | 262 |       2 | BW2aMLYN8CIF |  NAND        |
  +-----+---------+--------------+--------------+
  </code></pre>
  <p>I used <code>JOIN</code> with these three tables -</p>
  <pre><code>SELECT N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time 
  from notes AS N 
  JOIN notes_subject AS S ON N.user_id = 2 
    AND N.user_id = S.user_id 
    AND N.note_id = S.note_id 
  JOIN notes_tag AS T ON N.user_id = T.user_id 
    AND N.note_id = T.note_id 
  ORDER BY N.date DESC
  LIMIT 3
  </code></pre>
  <p>and it fetched -<br />
  <strong>Result 1 :</strong></p>
  <pre><code>+----+---------+--------------+--------------+----------+------------+----------+
  | id | user_id | note_id      | subject_name | tag_name | date       | time     |
  +----+---------+--------------+--------------+----------+------------+----------+
  |  7 |       2 | ARJuTcItPgbn | CN           | cn       | 2020-08-10 | 11:19:11 |
  |  7 |       2 | ARJuTcItPgbn | CN           |  IPV4    | 2020-08-10 | 11:19:11 |
  |  8 |       2 | BW2aMLYN8CIF | DLD          | AND      | 2020-08-10 | 11:24:35 |
  +----+---------+--------------+--------------+----------+------------+----------+
  </code></pre>
  <p>and If I run above query without <code>LIMIT</code>, it fetched -<br />
  <strong>Result 2 :</strong></p>
  <pre><code>+----+---------+--------------+--------------+--------------+------------+----------+
  | id | user_id | note_id      | subject_name | tag_name     | date       | time     |
  +----+---------+--------------+--------------+--------------+------------+----------+
  |  7 |       2 | ARJuTcItPgbn | CN           | cn           | 2020-08-10 | 11:19:11 |
  |  7 |       2 | ARJuTcItPgbn | CN           |  IPV4        | 2020-08-10 | 11:19:11 |
  |  8 |       2 | BW2aMLYN8CIF | DLD          | AND          | 2020-08-10 | 11:24:35 |
  |  8 |       2 | BW2aMLYN8CIF | DLD          |  NAND        | 2020-08-10 | 11:24:35 |
  |  1 |       2 | nVf0It70bnjQ | computer     | computer fan | 2020-08-08 | 11:18:17 |
  |  1 |       2 | nVf0It70bnjQ | computer     |  clean       | 2020-08-08 | 11:18:17 |
  |  3 |       2 | RfYg3u59Ytup | SQL          | sql          | 2020-08-06 | 12:50:51 |
  |  3 |       2 | RfYg3u59Ytup | SQL          | mysql        | 2020-08-06 | 12:50:51 |
  |  3 |       2 | RfYg3u59Ytup | SQL          |  select      | 2020-08-06 | 12:50:51 |
  |  3 |       2 | RfYg3u59Ytup | SQL          |  ASC         | 2020-08-06 | 12:50:51 |
  |  3 |       2 | RfYg3u59Ytup | SQL          |  DESC        | 2020-08-06 | 12:50:51 |
  +----+---------+--------------+--------------+--------------+------------+----------+
  </code></pre>
  <p>In the <strong>Result 1 :</strong> there are three rows of IDs (7,7,8) which are last inserted.  But result must be like -</p>
  <pre><code>+----+---------+--------------+--------------+--------------+------------+----------+
  | id | user_id | note_id      | subject_name | tag_name     | date       | time     |
  +----+---------+--------------+--------------+--------------+------------+----------+
  |  7 |       2 | ARJuTcItPgbn | CN           | cn           | 2020-08-10 | 11:19:11 |
  |  7 |       2 | ARJuTcItPgbn | CN           |  IPV4        | 2020-08-10 | 11:19:11 |
  |  8 |       2 | BW2aMLYN8CIF | DLD          | AND          | 2020-08-10 | 11:24:35 |
  |  8 |       2 | BW2aMLYN8CIF | DLD          |  NAND        | 2020-08-10 | 11:24:35 |
  |  1 |       2 | nVf0It70bnjQ | computer     | computer fan | 2020-08-08 | 11:18:17 |
  |  1 |       2 | nVf0It70bnjQ | computer     |  clean       | 2020-08-08 | 11:18:17 |
  +----+---------+--------------+--------------+--------------+------------+----------+
  </code></pre>
  <p>The result must be included with all the tag_name of a particular ID.</p>