asked on

get all customers

hi there ,
i have this query :
Select C.CID,
       C.NID,
       C.ID,
       C.LastName,
       C.FirstName,
       UpdateCode,
       PT.PayTypeName,
       CT.CancellKey,
       CT.CancellReson,
       Is_Cancell,
       RowType,
       ExportDate,
       EV,
       (select tg.ActionName from Tav_Gift tg where tg.ACTID = d.EV) as EventName,
       SibDate,
       SibsodSum
FROM Customers C
left join (
Select *
, row_number() over(partition by cid order by [SID] DESC) as rn
From Sibsodim
) d on c.cid=d.cid and d.rn=1

left join dbo.PaymentsMoves PM
ON C.CID = PM.CID
left join dbo.PayType PT
ON PM.MoveType = PT.PID
left join dbo.CancellTypes CT
on C.CancellResonId = CT.CancellKey
left join dbo.Tav_ShayHamarot
on C.CustCode = CodeNum
where PayTypeName = 'xxx'

in my customer table i have 100 customers
this query get only the customer that has row in dbo.PaymentsMoves but i want to see all the customers
how can i do it ?
thanks ...

ASKER CERTIFIED SOLUTION

funwithdotnet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Hi. look back at this answer: ID: 41746497

Remember that you had to change to a left join AND MOVE the condition to the JOIN

also: PLEASE do yourself a favour ALWAYS use table.column or alias.column

e.g. I have no idea which table PayTypeName = 'xxx' comes from, I can make a guess only

and when you look back at this query in 6 months time, or some other person looks at that query, there will always be the question "Which table does PayTypeName come from?"

I guess that PayTypeName is from PT (PT.PayTypeName), see line 11

SELECT ...

FROM Customers C
LEFT JOIN (
            SELECT
                    *
                  , ROW_NUMBER() OVER (PARTITION BY cid ORDER BY [SID] DESC) AS rn
            FROM Sibsodim
        ) d ON c.cid = d.cid AND d.rn = 1
LEFT JOIN dbo.PaymentsMoves PM ON C.CID = PM.CID
LEFT JOIN dbo.PayType PT ON PM.MoveType = PT.PID AND PT.PayTypeName = 'xxx'
LEFT JOIN dbo.CancellTypes CT ON C.CancellResonId = CT.CancellKey
LEFT JOIN dbo.Tav_ShayHamarot ON C.CustCode = CodeNum

Open in new window