How to Get Matching Records ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Get Matching Records !

Author

Topic

ishchopra
Starting Member

24 Posts

Posted - 2011-03-31 : 04:46:16

Hello Experts,

I have huge table with huge records, i am trying to find records who are partners as well as customers which means there is a possibility that with one name there would be two records like

Kaboom -- Partner
Kaboom -- Customer

My problem is that in few of the cases this both names does not match exactly and therefore it is not bring right results. i m using below mentioned query to get the results. please help me

SELECT DISTINCT
--Partner Details
A.NAMEX AS [Partner Name],
U1.Namex AS [Account Owner],
U2.Namex As [Sales Rep],
A.TYPEX as [Partner Type],
A.CORE_ACCOUNT_NUMBER AS [Partner ID],
A.Territory [Territory],
B.CORE_ACCOUNT_NUMBER as [Customer ID],
U3.Namex as [Customer Owner]
--PARTNER
from QACCOUNT A
--Account Owner
INNER JOIN QUSER AS U1
ON U1.ID = A.OwnerID and A.TERRITORY IN ('EMEA','NORDIC','BENELUX','DACH','middle east')
AND A.TYPEX ='partner' AND A.Typex <> 'Not Partner'
AND A.DELETE_FLAG = 'N' and A.customer ='true'
--Sales rep
left outer JOIN QUSER AS U2
ON U2.ID = A.Sales_Rep
--Customer Info
inner join QACCOUNT B on A.namex = B.namex
and B.Typex ='customer'
and B.TERRITORY IN ('EMEA','NORDIC','BENELUX','DACH','middle east')
--Customer Owner
left outer JOIN QUSER AS U3
ON U3.ID = B.OwnerID
--Sales rep
order by 1

I am using Namex to join because there is no other identical record.

Please help

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-31 : 05:16:36

can you send table with some dummy record

Raghu' S

ishchopra
Starting Member

24 Posts

Posted - 2011-03-31 : 05:18:42

Raghu,

there are 256 columns and its not possible to show in length.. can you please ask me question and i will try to explain through words ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 05:28:30

Use Common Table Expression, and include COUNT(name) OVER (PATITION BY name) AS NameCount
and at the end filter your query with WHERE NameCount >1

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158864

--------------------------
http://connectsql.blogspot.com/

ishchopra
Starting Member

24 Posts

Posted - 2011-03-31 : 05:30:58

Can you please alter my query above ? apologize for asking too much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources