this really bugs me - 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

this really bugs me

Author

Topic

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-05-28 : 09:49:50

so... i have a client table that has 27000 rows ... and in that table I have multiple entries with the same name but different client number or different bank_account_number ...

so I need to filtrate the duplicate entries ... by grouping the client name to the multiple clientnumbers or bank numbers....

so client xxxx --- 1213 --
xxxx --- 1214 --

until now I've tried something like this :

select t1.name ,t3.id ,t2.codfiscal from

(select * from client ) t1

left outer join

( select codfiscal from client
group by codfiscal
having count(codfiscal)>1

) t2 on t1.codfiscal=t2.codfiscal

inner join (
select id from client
group by id
having count(id)>1
) as t3 on t1.id=t3.id

order by t1.name asc

any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:59:57

Not without seeing your table structures.
And some sample data.
And some proper expected output.

E 12°55'05.63"
N 56°04'39.26"

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-05-28 : 10:08:35

table structure :
CREATE TABLE [dbo].[Client](
[CompanyId] [int] NOT NULL CONSTRAINT [DF_Client_CompanyId] DEFAULT (1),
[Id] [int] NOT NULL,
[Name] [nvarchar](120) NULL,
[PostCode] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Street] [nvarchar](50) NULL,
[HouseNumber] [nvarchar](50) NULL,
[PayType] [char](1) NULL,
[Branch] [char](1) NULL,
[AlBranch] [char](2) NULL,
[TelKor] [char](6) NULL,
[TelNum] [char](15) NULL,
[Email] [char](50) NULL,
[Admin] [char](10) NULL,
[DateofAdmin] [datetime] NULL,
[CodFiscal] [nvarchar](50) NULL,

sample data :

ACS ACTIVE CENTER SRL 10081 ROR 4724596
AD COMPUTER NETGATE SRL 10086 ROR 13611537
AD D VERITAS 10432 ROR 14974506
AD LINE SRL 13149 ROR 15498656

expected data :

ACCELUM SRL 10073 ROR 9881192
ACELLUM SRL 10424 ROR 9881192
ABSTARCT CONNECTION TIMISOARA 10068 ROR 15060973
ABSTRACT CONNECTION TIMISOARA 11143 ROR 15060973

the client number differs but the name is the same ... so I can group them into an excel table ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-28 : 10:48:43

Maybe I'm missing something, but the sample data doesn't fit into the table that you gave and I can't seen any relationship between the sample info and the expected output. The names in the output don't appear in the sample, same with the 2nd and 4th columns.

For it to be at all useful, sample data should have the same format as the table and the expected output should be based on the sample data.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 11:33:58

its not evident from posted data which field is client number, but it will be something like


SELECT t.*
FROM YourTable t
INNER JOIN (SELECT ClientName
            FROm YourTable
            GROUP BY ClientName
            HAVING COUNT(DISTINCT ClientNo)>1
           )t1
ON t1.ClientName=t.ClientName

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-05-29 : 02:14:24

someone?

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-05-29 : 02:55:32

quote:
Originally posted by GilaMonster

Maybe I'm missing something, but the sample data doesn't fit into the table that you gave and I can't seen any relationship between the sample info and the expected output. The names in the output don't appear in the sample, same with the 2nd and 4th columns.

For it to be at all useful, sample data should have the same format as the table and the expected output should be based on the sample data.

--
Gail Shaw
SQL Server MVP

so... as i mentioned in my first post... I only need those 3 columns... the rest.. it's not useful ... so I need the duplicate names... to have in the other 2 columns.. the diferent client no or bank no .... like client xxx ---with client no 123 and bank no 345
and then still client xxx --- with other client no 321 and bank no 345
so a duplicate client name could have 2 diferences.. one in the client no column and/or bank no

any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 03:11:20

i think you missed Gail's point.

quote:
Originally posted by GilaMonster

Maybe I'm missing something, but the sample data doesn't fit into the table that you gave and I can't seen any relationship between the sample info and the expected output. The names in the output don't appear in the sample, same with the 2nd and 4th columns.

For it to be at all useful, sample data should have the same format as the table and the expected output should be based on the sample data.

--
Gail Shaw
SQL Server MVP

can you explain how to get from


sample data :

ACS ACTIVE CENTER SRL 10081 ROR 4724596
AD COMPUTER NETGATE SRL 10086 ROR 13611537
AD D VERITAS 10432 ROR 14974506
AD LINE SRL 13149 ROR 15498656


expected data :

ACCELUM SRL 10073 ROR 9881192
ACELLUM SRL 10424 ROR 9881192
ABSTARCT CONNECTION TIMISOARA 10068 ROR 15060973
ABSTRACT CONNECTION TIMISOARA 11143 ROR 15060973

KH
[spoiler]Time is always against us[/spoiler]

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-05-29 : 04:05:55

the names differ... those are totally random selections.... IF u read what I posted u will understand... the expected data only suggest... that the duplicate names have different client numbers or bank numbers... I found those in the expected data and put them in my post only to suggest what I want to obtain !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 04:16:06

You are not helping us to understand your problem by providing random result that is not corresponding to the sample data provided.

What we are expecting is you can post the sample data and the expected result from the sample data you posted.

KH
[spoiler]Time is always against us[/spoiler]

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-05-29 : 05:56:17

this is what I expect ... all grouped like this... but only the ones that have duplicates in the name column... and right next to it are the client_no column... witch are different on every case... because not one client has the same client number....
so ... expected data :
name Client_no Bank_no

SECA DISTRIBUTION SRL 10540 RO5522510
SECA DISTRIBUTION SRL 10541 ROR5522510

random data :

NAME Client_no Bank_no

ASOC DE PROP SEL 28-30 10894 RO11379369
IN MEMORIAM SRL 10901 RO3861404
AJVPS 10909 RO4919121
BAD AXEL SRL 10913 ROR 1283541
AQUILA PART PROD COM SRL 10916 RO6484554
SCM CONSTRUCTORUL 10919 RO74148
MOBIL TILEAGD SA 10925 ROR 117230
TELECONSTRUCTIA SA 10954 ROR217205
FARMEXPERT DCI 10986 ROR13758814
PANGRAM SA 10992 RO6695683
WORLD MEDIATRANS SRL 11014 RORO9480727
BAD AXEL SRL 11067 ROR 1283541
VOLKSBANK ROMANIA SA 11096 RO15014365
AUTOTOTAL SRL 11101 ROR14847561
EUROM BANK 11129 ROR4905630
ANKO POMP SRL 11130 ROR4905630
TEMAD CO SRL 11139 RO6620400
SUNLIGHT ROMANIA SRL 11142 RO14525582
FINISIA 11149 NULL
INTERSTAR CHIM SA 11154 ROR 6614930
MUNAX SRL 11156 ROR 15380528
FINISIA 11158 ROR 11616325
PF GABOR GAVRIL 11163 RO1760510054679
RELUXA COM 11186 NULL
RELUXA COM 11187 ROR 5397476
STARTELEFONICA SRL 11238 NULL
FUNDATIA GABOR DENES 11246 ROR 7111863
NEW START SRL 11264 ROR 65921
JT INTERNATIONAL ROMANIA SRL 11272 RO5110535
ZAGROS SRL 11324 RO3686940
ATLASSIB SRL 11327 RO18162471
WEST PALACE SRL 11339 RO6734936
VALROM INDUSTRIE SRL 11345 RO8529679
COMFRUCT SA 11363 ROR47208
BIZOOFRUCT BIHARIA SA 11373 RO113174
COMPUTER FORCE SRL 11390 RO4819289
GENERALI ASIGURARI SA 11410 RORO 4134668
COMCEREAL SA 11414 ROR 8216582
UNISEM SA 11418 RORO9148540
PRIMA GUARD SECURITY GROUP 11466 ROR11267756
NICOLIMP SRL 11494 ROR CUI 1590361
PF GABOR GAVRIL 11508 NULL
PPS 11509 NULL
PPS 11514 ROCUI 14939282
FUSTELLIFICIO AGRI PROD SRL 11528 ROR9358283

So... u will see that there are duplicates... but I dont wanna select only those... anything else is useless !

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-29 : 11:52:19

quote:
so... as i mentioned in my first post... I only need those 3 columns... the rest.. it's not useful ... so I need the duplicate names

quote:
So... u will see that there are duplicates... but I dont wanna select only those... anything else is useless !

I'm still confused as to what you want. I *think* you are asking for Clients with the same Name that have different IDs and CodFiscal values. Is that correct?

SELECT DISTINCT
	Name,
	ID,
	CodFiscal
FROM 
	Client
INNER JOIN 
	(
		SELECT Name
		FROM Client
		GROUP BY Name
		HAVING COUNT(Name) > 1
	) AS T
	ON Client.Name = T.Name

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-29 : 12:43:16

Also, try following the first link in my signature. It will show you how to give sample data and output that are in a usable format.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources