?
Solved

get all customers

Posted on 2016-08-10
2
Medium Priority
?
67 Views
Last Modified: 2016-08-10
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 ...
0
Comment
Question by:Tech_Men
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
funwithdotnet earned 2000 total points
ID: 41751173
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41751254
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question