August 21, 2013 at 12:32 pm
Not sure why but any suggestions?
Select Distinct
M.Memid,
M.Fullname,
E.enttype,
EK.planid,
Ek.Carriermemid HICN,
EK.effdate EffectiveDate,
BP.upid ContractPBP,
PA.CreateDate,
PA.Comment,
PA.[Source],
PA.CompleteDate
From dbo.Member M
Left Join dbo.Entity E
on E.entid=M.entityid
Join dbo.EnrollKeys Ek
on EK.Memid=M.Memid
Join dbo.BenefitPlan BP
on BP.Planid=EK.planID
Join dbo.PlanAction PA
on PA.secondid=E.planactionsecid
WHERE PA.CompleteDate is Null
and PA.[Source]='ID Card'
and (ek.termdate > GETDATE())
August 21, 2013 at 12:51 pm
It is difficult to guess just by reading the query. It will be helpful if you describe in words what is it that you are trying to accomplish.
Something that call my attention is that you are using an OUTER JOIN between [Member] and [Entity] but and INNER JOIN between [Entity] and [PlanAction] and then you filter by some columns from [PlanAction].
This is not easy to digest, not by me.
August 21, 2013 at 1:03 pm
lisa.ogle (8/21/2013)
Not sure why but any suggestions?Select Distinct
M.Memid,
M.Fullname,
E.enttype,
EK.planid,
Ek.Carriermemid HICN,
EK.effdate EffectiveDate,
BP.upid ContractPBP,
PA.CreateDate,
PA.Comment,
PA.[Source],
PA.CompleteDate
From dbo.Member M
Left Join dbo.Entity E
on E.entid=M.entityid
Join dbo.EnrollKeys Ek
on EK.Memid=M.Memid
Join dbo.BenefitPlan BP
on BP.Planid=EK.planID
Join dbo.PlanAction PA
on PA.secondid=E.planactionsecid
WHERE PA.CompleteDate is Null
and PA.[Source]='ID Card'
and (ek.termdate > GETDATE())
1) anything you LEFT join to MUST ALSO be LEFT joined to for all the rest of the query, otherwise you essentially convert it into an INNER JOIN
2) anything you LEFT join to will ALSO be essentially converted to an INNER JOIN if you put a WHERE clause constraint on data from that table. Where happens AFTER joining. If you need to filter LEFT JOINed table, put the conditional(s) in the JOIN clause
VERY common mistakes above, so don't feel bad! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 1:07 pm
So I should just use Join then all the way? Thanks I will try that.
August 21, 2013 at 2:57 pm
lisa.ogle (8/21/2013)
So I should just use Join then all the way? Thanks I will try that.
NO, I did NOT say that!! 🙂 You should use TSQL as necessary to get the correct output (and then hopefully most efficiently, but correctness must come first obviously).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply