CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Aamir Shah
Aamir Shah

Reputation: 83

Why left join doesn't work but right join works ?

i have 3 tables,

Transport.Devices, Transport.Conductors, Transport.ConductorDevices,

i am applying LeftJoin on Devices table so it should also display those Devices which are not assigned, but it doesn't work , why ? i tried Right join and it worked but left doesn't work.

Select Transport.ConductorDevices.ID, Transport.ConductorDevices.Device_ID,Transport.ConductorDevices.Conductor_ID,
        Transport.Conductors.Name as Conductor, Transport.Devices.TerminalSNO as Terminal
        from Transport.ConductorDevices
        Inner Join Transport.Devices
        ON Transport.Devices.DeviceID=Transport.ConductorDevices.Device_ID
        left Join Transport.Conductors
        ON Transport.Conductors.ConductorID= Transport.ConductorDevices.Conductor_ID

Upvotes: 0

Views: 588

Answers (2)

cypizek
cypizek

Reputation: 337

If you need all devices (not assigned too) you can start FROM with devices:

 from Transport.Devices
    LEFT JOIN Transport.ConductorDevices
    ON Transport.Devices.DeviceID=Transport.ConductorDevices.Device_ID
    LEFT JOIN Transport.Conductors
    ON Transport.Conductors.ConductorID= Transport.ConductorDevices.Conductor_ID

Upvotes: 0

Peter
Peter

Reputation: 27944

A left join != right join, a left join does include all on the left and the right join includes all on the right. In this case you need all on the right. That is why the right join is working for you.

sql_join_left

Upvotes: 2

Related Questions