Francisunoxx Francisunoxx - 4 months ago 20
SQL Question

How to Left Join with Inner Join

I have a tables users and documents which has a many to many relationship with document_user (junction table). But my documents table has a column category_id that reference to categories table. So categories has a one to many relationship with documents. See screenshot below.

Screenshot

I want to join in my junction table the category_id under documents table.

This is my code so far.

SELECT DU.user_id, DU.document_id, DU.dateReceived FROM document_user DU
INNER JOIN documents D ON DU.document_id = D.id
INNER JOIN users S ON DU.user_id = S.id;


Result:

enter image description here

Answer

Add another join, as said in the comments:

SELECT DU.user_id, DU.document_id, DU.dateReceived, C.category_type
FROM document_user DU
INNER JOIN documents D ON DU.document_id = D.id
INNER JOIN users S ON DU.user_id = S.id
INNER JOIN categories C ON C.id = D.category_id;