Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 left join not displaying all records

Author  Topic 

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2014-02-25 : 02:13:09
Hello I have this query that should produce about 600 records (for each student) and it works fine until I add an additional filter in the where clause by which reduces the number of records to about half. I need the condition be applied to display all the records. Any suggestions greatly appreciated. The additional filter is (a.DT >= '2013-09-01')


DECLARE @SC INT = 30, @GR INT = 9;

SELECT
s.FN + ' ' + LN AS NAME ,
s.ID ,
s.GR ,
s.SC ,

( CASE WHEN s.RC1 = 700
AND ETH = 'Y' THEN 'Hispanic'
WHEN s.RC1 = 600
AND ETH = 'N' THEN 'African American'
ELSE 'Other'
END ) AS Ethnicity ,
( CASE WHEN s.U7 = 'G' THEN 'Yes'
ELSE 'No'
END ) AS Gate ,
( CASE WHEN s.U9 IN ( 'R', 'D', 'S' ) THEN 'Yes'
ELSE 'No'
END ) AS SpecialEd ,
( CASE WHEN s.LF = '1' THEN 'English Only'
WHEN s.LF = '2' THEN 'Initially Fluent English Proficient'
WHEN s.LF = '3' THEN 'English Learner'
WHEN s.LF = '4' THEN 'Redesignated Fluent English Proficient'
WHEN s.LF = '5' THEN 'TBD'
END ) AS LF ,

CASE WHEN SUM(a.PID) >= 2
AND SUM(CASE WHEN a.DS = 'SUS' THEN 1
ELSE 0
END) >= 1 THEN 'No'
ELSE 'Yes'
END AS [On Target?]
FROM dbo.STU AS s
LEFT JOIN dbo.ADS AS a ON a.PID = s.ID
WHERE (a.DT >= '2013-09-01') AND
s.GR = @GR
AND s.SC = @SC
GROUP BY s.ID ,
s.GR ,
s.SC ,
s.FN ,
s.LN ,
RC1 ,
s.ETH ,
s.U7 ,
s.U9 ,
s.LF



Cartesian Yak

RonnieRahman
Starting Member

6 Posts

Posted - 2014-02-25 : 07:41:12
How about using a CTE first to filter your records and then LEFT JOIN to the CTE.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:29:20
As per email, move the date filter to the join.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-02-25 : 19:18:48
By requiring a value for the non-preserved (other) table of the outer join in the where clause, you have converted it to an inner join.

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)
Go to Top of Page
   

- Advertisement -