CODEWITHSUNDEEP
sqlsql-serverjoin
ABCDE
ABCDE

Reputation: 131

How to join three tables in sql server

I have three tables:

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.

Upvotes: 1

Views: 27795

Answers (4)

Jyothish Bhaskaran
Jyothish Bhaskaran

Reputation: 559

Select * From Chapter c
   Left Join Lesson l On c.ChapterNo=l.ChapterNo  
   Left Join Case cs On cs.LessonNo=l. LessonNo

Upvotes: 1

mansi
mansi

Reputation: 877

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

Upvotes: 1

GNMercado
GNMercado

Reputation: 433

Use Left Join query in order to get your desired result

As for naming of tables. Don't use Case as table name because it is a reserved word for SQL.

SELECT CH.ChapterNo,CH.Desc,LE.LessonNo,LE.Dec,CA.CaseNo,CA.Desc FROM Chapter CH LEFT JOIN Lesson LE ON CH.ChapterNo = LE.ChapterNo LEFT JOIN Cases CA ON LE.LessonNo = CA.LessonNO

Upvotes: 2

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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

Upvotes: 11

Related Questions