Get Last N Records After Join In Sql?

I am creating a website for myself where I can create notes on any subjects. For creating a note I have to give subject name, topic, tags, note content. Whenever I press save butto

Solution 1:

You can use a subquery to select the most recent notes, and then JOIN:

select N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time
from (select N.*
      from notes N
      where N.user_id = 2orderby N.date desc, N.time DESC
      limit 3
     ) N join
     notes_subject S  
     on N.user_id = S.user_id and
        N.note_id = S.note_id join
     notes_tag T 
     on N.user_id = T.user_id and
        N.note_id = T.note_id 
orderby N.date DESC, N.time DESC;

I am guessing that you want the most recent notes, even if the other tables do not match. For this, use left joins:

select N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time
from (select N.*
      from notes N
      where N.user_id = 2orderby N.date desc, N.time DESC
      limit 3
     ) N left join
     notes_subject S  
     on N.user_id = S.user_id and
        N.note_id = S.note_id left join
     notes_tag T 
     on N.user_id = T.user_id and
        N.note_id = T.note_id 
orderby N.date desc, N.time desc;

If you want only notes that match both tables, use dense_rank():

select N.*
from (select N.id, N.user_id, N.note_id, S.subject_name, T.tag_name, N.date, N.time,
             dense_rank() over (orderby N.date desc, N.time desc, n.id) as seqnum
      from notes N join
           notes_subject S  
           on N.user_id = S.user_id and
              N.note_id = S.note_id join
           notes_tag T 
           on N.user_id = T.user_id and
              N.note_id = T.note_id 
      ) N
where seqnum <= 3orderby N.date desc, N.time desc;

Solution 2:

Another approach would be to group by note_id and GROUP_CONCAT the tags, you can break them out later on the frontend with JavaScript (assuming you use JS on the front-end). You can configure the separator as well.

To reverse you can flip the ORDER BY from DESC to ASC and put any number after TOP.

MYSQL GROUP CONCAT

SELECT TOP 3
    N.id,N.user_id,N.note_id ,S.subject_name, GROUP_CONCAT(T.tag_name),N.date, N.time from notes AS N 
JOIN notes_subject AS S ON N.user_id = 2AND 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 
GROUPBY note_id
    ORDERBY N.date DESC, time DESC;