When I put my Where clause on Qry will not return data

  • 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())

  • 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.

  • 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

  • So I should just use Join then all the way? Thanks I will try that.

  • 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