Subqueries not returning all records

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 2005 Forums

Transact-SQL (2005)

Subqueries not returning all records

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2011-08-24 : 10:44:14

I have the following query that I am trying to run in SQL Server 2005, however not all of the records from query a are being displayed.
Can you help please ?

Select '""', a.CANDa, a.PWQ, a.PWA, a.DaysAccd, b.DaysPaid

From
(Select Distinct CANDIDATE.Candidate_Code as Canda, Count(WTR_Id)as PWQ, Count(WTR_Id) as PWA, Sum(Holiday_Days) as DaysAccd
From PAYE_WTR Inner Join PAYE on PAYE_WTR.Paye_Id = PAYE.Paye_Id
Inner Join PERSON on PAYE.Person_Id = Person.Person_Id
Inner Join CANDIDATE on PERSON.Person_Id = CANDIDATE.Person_Id
Where PAYE_WTR.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code) a,

(Select Distinct CANDIDATE.Candidate_Code as Candb, Sum(Item_Quantity) as DaysPaid
From PAYSLIP Inner Join PAYSLIP_ITEM on PAYSLIP.Payslip_Id = Payslip_Item.Payslip_Id
Inner Join PAYE ON PAYSLIP.PAYE_Id = PAYE.PAYE_Id
Inner Join PERSON ON PAYE.Person_Id = PERSON.Person_Id
Inner Join CANDIDATE ON PERSON.Person_Id = CANDIDATE.Person_Id
Where Item_Description like '%Holiday Pay%' and PAYSLIP_ITEM.Code_Id=3
and PAYSLIP_ITEM.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code) b

Where a.CANDa = b.CANDb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 11:57:13

thats because you're doing inner join. so unless it has a matching record in b it wont be returned. use modified query below if you want all records from a regardless of match in b


Select '""', a.CANDa, a.PWQ, a.PWA, a.DaysAccd, b.DaysPaid 

From 
(Select Distinct CANDIDATE.Candidate_Code as Canda, Count(WTR_Id)as PWQ, Count(WTR_Id) as PWA, Sum(Holiday_Days) as DaysAccd
From PAYE_WTR Inner Join PAYE on PAYE_WTR.Paye_Id = PAYE.Paye_Id
Inner Join PERSON on PAYE.Person_Id = Person.Person_Id
Inner Join CANDIDATE on PERSON.Person_Id = CANDIDATE.Person_Id
Where PAYE_WTR.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code)	a
left join
(Select Distinct CANDIDATE.Candidate_Code as Candb, Sum(Item_Quantity) as DaysPaid
From PAYSLIP Inner Join PAYSLIP_ITEM on PAYSLIP.Payslip_Id = Payslip_Item.Payslip_Id
Inner Join PAYE ON PAYSLIP.PAYE_Id = PAYE.PAYE_Id 
Inner Join PERSON ON PAYE.Person_Id = PERSON.Person_Id
Inner Join CANDIDATE ON PERSON.Person_Id = CANDIDATE.Person_Id 
Where Item_Description like '%Holiday Pay%' and PAYSLIP_ITEM.Code_Id=3
and PAYSLIP_ITEM.Reference_Year=2011 and CANDIDATE.Active_YN = 'Y'
Group By CANDIDATE.Candidate_Code)	b
on a.CANDa = b.CANDb

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2011-08-25 : 03:50:13

Thanks very much, that works perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 04:49:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources