Need Assistance with SQL Query

[caper11752]

Hey folks, I'm struggling with a query and i'm hoping someone can help me out.

I have 3 tables:

(1) tblClientGroups
 GroupID (PK)
 ClientID
 GroupName
 SortOrder
 ReportGrouping
 Archived

(2)tblGroupJobs
 JobID
 GroupID
 JobName
 SortOrder
 Archived

(3)tblWorkedItems
 WorkedItemID (PK)
 WorkedItemJobID
 WorkItemCount
 DateModified

What I need my query to be able to do is to return all rows from tables 1 & 2 all the time, but only those rows from table 3 where DateModified = a specific date. Below is the query I have right now but not all of the records from the 1st and 2nd tables are being returned. It appears that they do get returned if I remove WHERE clause but I need to filter by date. So if there was not corresponding rows in the 3rd table I'll expect to see NULL values.

SELECT
	CG.GroupID
	,CG.GroupName
	,CG.ClientID
	,CG.ReportGrouping
	,GJ.JobID
	,GJ.JobName
	,Convert(varchar,WI.DateModified,101) as DateModified
	,WI.WorkedItemID
	,WI.WorkItemCount

FROM [UTT].[dbo].[tblClientGroups] CG

LEFT JOIN [UTT].[dbo].[tblGroupJobs] GJ
	ON CG.GroupID = GJ.GroupID 
LEFT JOIN [UTT].[dbo].[tblWorkedItems] WI
	ON GJ.JobID = WI.WorkedItemJobID 
	
WHERE Convert(varchar,WI.DateModified,101) = '03/24/2013'

ORDER BY GJ.JobID

Basically, tables 1 and 2 would be more or less static and table 3 results would filtered on DateModified. Can this be done?

Thanks if advance!

 

[gmmastros]

try this:

SELECT
	CG.GroupID
	,CG.GroupName
	,CG.ClientID
	,CG.ReportGrouping
	,GJ.JobID
	,GJ.JobName
	,Convert(varchar,WI.DateModified,101) as DateModified
	,WI.WorkedItemID
	,WI.WorkItemCount

FROM [UTT].[dbo].[tblClientGroups] CG

LEFT JOIN [UTT].[dbo].[tblGroupJobs] GJ
	ON CG.GroupID = GJ.GroupID 
LEFT JOIN [UTT].[dbo].[tblWorkedItems] WI
	ON GJ.JobID = WI.WorkedItemJobID 
	[!]AND[/!] Convert(varchar,WI.DateModified,101) = '03/24/2013'

ORDER BY GJ.JobID

If this works, let me know and I will explain why it works.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

What do you mean with "return all rows from tables 1 & 2 all the time"?
Your query returns ALL records from tblClientGroups and only matching records from tblGroupJobs.
Maybe you need FULL JOIN instead of LEFT JOIN?

When you need to have LEFT JOIN and if you want this JOIN to return pnly a part of the records put the condition in the JOIN not in WHERE clause.
When you put the condition in WHERE clause you made this join INNER.

SELECT
	CG.GroupID
	,CG.GroupName
	,CG.ClientID
	,CG.ReportGrouping
	,GJ.JobID
	,GJ.JobName
	,Convert(varchar,WI.DateModified,101) as DateModified
	,WI.WorkedItemID
	,WI.WorkItemCount
FROM [UTT].[dbo].[tblClientGroups] CG
LEFT JOIN [UTT].[dbo].[tblGroupJobs] GJ  ON CG.GroupID = GJ.GroupID 
LEFT JOIN [UTT].[dbo].[tblWorkedItems] WI ON GJ.JobID = WI.WorkedItemJobID  AND WI.DateModified >= '20130324' AND WI.DateModified < '20130325'
ORDER BY GJ.JobID

Borislav Borissov
VFP9 SP2, SQL Server