Get the values ​​of all rows in the left join

Question

I have 3 tables. The first table is the main table, the second table I leave joins it with the main table, and the third table I leave joins it with the second table. So it looks like this:

Main Table
Order  Item  Supplier
1      1      X
1      2      X

Second Table
Order  LineNumber
1      22

Third Table
LineNumber  Name
22          F

After completing the connection, I get:

Order  Item  Supplier  Name
1      1     X         F
1      2     X         null

This is what I want:

Order  Item  Supplier  Name
1      1     X         F
1      2     X         F

How can I achieve this goal? This is my code:

select *, third.Name from main left join second on main.order = second.order left join third on second.LineNumber = third.LineNumber

Sometimes the main table will contain orders that are not in the second table, that's why I use a left join. The same goes for the third table and the second table

Answer 1

Try this

select main.Order,Item,Supplier,Name from main 
left join second on main.Order = second.Order  
left join third on second.LineNumber = third.LineNumber