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 ...
Tech_MenAsked:
Who is Participating?
 
funwithdotnetConnect With a Mentor Commented:
Well, you could try commenting
--where PayTypeName = 'xxx'

... for starters, since PayTypeName is in PaymentsMoves table. As long as you are filtering based on data in that table, that's all you'll get.

Good luck!
0
 
PortletPaulfreelancerCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.