CODEWITHSUNDEEP
sqlsql-server-2008
newdimension
newdimension

Reputation: 337

SQL JOIN not filtering correctly

My SQL statement was showing the data properly until I added the last two JOIN statements. I need to somehow properly "link" them to the data.

My statement:

FROM dbo.DailyWork AS dw
LEFT JOIN dbo.Labor as lab
ON dw.DailyWorkID=LAB.DailyWorkID
LEFT JOIN dbo.Company AS com
ON dw.CompanyID=com.CompanyID
LEFT JOIN dbo.Projects AS proj
ON dw.ProjectID=proj.ProjectID
JOIN dbo.LU_Trade AS trade
ON lab.TradeID=lab.TradeID
JOIN dbo.LU_Classification as class
ON lab.ClassID=class.ClassID

Relevant tables:

Dailywork

SELECT [DailyWorkID]
      ,[Date]
      ,[CompanyID]
      ,[CrewID]
      ,[WorkDescription]
      ,[LastModified]
      ,[LastModifiedBy]
      ,[ProjectID]
      ,[GUID]
      ,[IsClosed]
      ,[TypeID]
      ,[CategoryID]
      ,[Status]
      ,[IsFromCrewSight]
  FROM [dbo].[DailyWork]

Labor

SELECT [LaborID]
      ,[DailyWorkID]
      ,[EmployeeID]
      ,[StartTime]
      ,[BreakTime]
      ,[FinishTime]
      ,[STQty]
      ,[OTQty]
      ,[DTQty]
      ,[TotalSTQty]
      ,[TotalOTQty]
      ,[TotalDTQty]
      ,[CostCodeReference]
      ,[Notes]
      ,[TradeID]
      ,[ClassID]
      ,[ManpowerQty]
      ,[UOM]
      ,[TotalManQtyPerCrew]
      ,[TotalManQtyPerCompany]
      ,[TotalManpowerQty]
      ,[SortOrder]
      ,[LastModified]
      ,[LastModifiedBy]
      ,[ProjectID]
      ,[GUID]
      ,[IsFromCrewSight]
  FROM [dbo].[Labor]

LU_Trade

SELECT [TradeID]
      ,[Description]
      ,[UserText1]
      ,[UserNum1]
      ,[SortOrder]
  FROM [dbo].[LU_Trade]

LU_Classification

SELECT [ClassID]
      ,[Description]
      ,[UserText1]
      ,[UserNum1]
      ,[SortOrder]
  FROM [dbo].[LU_Classification]

My first thought is that I should have used dbo.Labor as my FROM statement because it combines most of the columns. However, I still don't know how to "link" the Trade and Classification to the logic.

Upvotes: 0

Views: 727

Answers (2)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

It has to do with what you are joining on. Initially you had Labor Left Joined to DailyWork. The nature of the left join is to only bring back additional data and keep rows of DailyWork that don't match anything on the Labor table.

When you added the last two tables, you used JOIN which is equivalent to an INNER JOIN. When the query tried to join on the NULL values generated by the LEFT JOIN of the Labor table, those rows dropped out.

If you changed the last two joins in your original query to use a LEFT JOIN like below, you should get the correct rows.

FROM dbo.DailyWork AS dw
LEFT JOIN dbo.Labor as lab
ON dw.DailyWorkID=LAB.DailyWorkID
LEFT JOIN dbo.Company AS com
ON dw.CompanyID=com.CompanyID
LEFT JOIN dbo.Projects AS proj
ON lab.ProjectID=proj.ProjectID
LEFT JOIN dbo.LU_Trade AS trade
ON lab.TradeID=trade.TradeID
LEFT JOIN dbo.LU_Classification as class
ON lab.ClassID=class.ClassID

On a side note, if your first two tables are interchangeable, do you need to use left joins for all of the tables that follow?

Upvotes: 2

newdimension
newdimension

Reputation: 337

I rewrote the JOIN statement to be based on the Labor table. It seems to have fixed it.

FROM dbo.Labor as lab
LEFT JOIN dbo.DailyWork AS dw
ON lab.DailyWorkID=dw.DailyWorkID
LEFT JOIN dbo.Company AS com
ON dw.CompanyID=com.CompanyID
LEFT JOIN dbo.Projects AS proj
ON lab.ProjectID=proj.ProjectID
JOIN dbo.LU_Trade AS trade
ON lab.TradeID=trade.TradeID
JOIN dbo.LU_Classification as class
ON lab.ClassID=class.ClassID

Now I'm curious, is the only way to combine information from different tables, is to have a table to combines all the columns ?

Upvotes: 1

Related Questions