ABCDE ABCDE - 1 month ago 12
SQL Question

How to join three tables in sql server

I have three tables:


  • Chapter (ChapterNo, Desc)

  • Lesson (LessonNO, Dec, ChapterNo)

  • Case (CaseNo, Desc, LessonNo)



As you see, the three tables are all connected. What I want is to return all the chapters (even without lessons in it), all the lessons (even without cases in it) and all the cases.

I am sorry to say but I really don't have an idea so I have no sample code. I hope my explanations are enough.

Answer

USE LEFT JOIN:

SELECT
    C.*,
    L.*,
    CA.*
FROM Chapter C
LEFT JOIN Lesson L ON C.ChapterNo=L.ChapterNo
LEFT JOIN Case CA ON L.LessonNo=CA.LessonNo