SQL not working

I have the sql below. It will display a list of billing entries where people owe moneys.

Problem is the following.

I am passing two parameters, one is "MMColParam2" and "MMColParam3"

For the first one I have an option to display ALL records even if the join is non-existent

        LEFT JOIN Users AS c ON c.userid = cases.EmpId

I pass the value "%", and use the LIKE clause. It should display the results even if  cases.Empid has no value.

But it doesn't, it only displays results in which cases.Empid has a value.  What am I missing ?

Does it have to do with the group clause where the value is included ?  If I take it out it the query breaks ... what to do ?

---- Full query -----


SELECT  dbo.Cases.Id ,
        dbo.Cases.CaseId ,
        dbo.Cases.FirmId ,
        dbo.Cases.AlienId ,
        dbo.Users.UserId ,
        dbo.Users.MailCareOf ,
        dbo.Users.MailStr ,
        dbo.Users.MailApt ,
        dbo.Users.MailCity ,
        dbo.Users.MailState ,
        dbo.Users.MailZip ,
        dbo.Users.MailCntry ,
        dbo.Users.LastNm ,
        dbo.Users.FirstNm ,
        BillingLines.CaseId AS BCaseId ,
        SUM(BillingLines.UnitCost * Qty) AS SUM ,
        SUM(BillingLines.PmtRecd) AS RECTOT ,
        ( SUM(BillingLines.PmtRecd) ) - ( SUM(BillingLines.UnitCost
                                              * BillingLines.Qty) ) AS GRANDTOT ,
        c.MaidenNm AS EmployerName ,
        d.Category AS Category
FROM    ( ( dbo.Cases
          INNER JOIN dbo.Users ON dbo.Cases.AlienId = dbo.Users.UserId)
        )
        INNER JOIN dbo.BillingLines ON dbo.Cases.Id = dbo.BillingLines.CaseId
        LEFT JOIN Users AS c ON c.userid = cases.EmpId
        LEFT JOIN dbo.BillingItems ON dbo.Billingitems.itemid = dbo.BillingLines.Itemid
        LEFT JOIN BillingCategories AS d ON BillingItems.catalogid = d.billingcategoryid
WHERE   Cases.FirmId = MMColParam
        AND cases.archived <> 1
        AND cases.EmpId LIKE 'MMColParam2'
        AND d.BillingCategoryid LIKE 'MMColParam3'
GROUP BY dbo.Cases.Id ,
        dbo.Cases.CaseId ,
        dbo.Cases.FirmId ,
        dbo.Cases.AlienId ,
        dbo.Users.UserId ,
        dbo.Users.LastNm ,
        dbo.Users.FirstNm ,
        BillingLines.CaseId ,
        dbo.Users.MailCareOf ,
        dbo.Users.MailStr ,
        dbo.Users.MailApt ,
        dbo.Users.MailCity ,
        dbo.Users.MailState ,
        dbo.Users.MailZip ,
        c.MaidenNm ,
        d.Category ,
        dbo.Users.MailCntry
HAVING  ( SUM(BillingLines.PmtRecd) ) - ( SUM(BillingLines.UnitCost
                                              * BillingLines.Qty) ) <> 0
ORDER BY cases.caseid
---
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COANetworkCommented:
"cases.EmpID" does not appear in your GROUP BY clause, so it cannot be the cause, anyway.  However, a "LIKE" statement will not pick up NULLs.  you need to trap for that.  do one of the following:
AND ISNULL(cases.EmpId, '') LIKE 'MMColParam2'

Open in new window

or
AND (cases.EmpId IS NULL OR cases.EmpId LIKE 'MMColParam2')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.