How to get exclude the name IF the name is more than once

Question

1.00/5 (1 vote)

See more:

This is my table

EmpID      EmpName          CompanyName                            CompanyID
123         Josep        Kramer Levin Naftalis & Frankel LLP            468
123         Josep        Thompson Hine LLP                              567
801         Simon        Ogletree Deakins International LLP             222
801         Simon        Ogletree, Deakins, Nash PC                     916
602         alen         Baker Co Ltd                                   732
602         alen         Baker Mcken  Ltd                               242

Condition is : Output will return, if the first word of the company name is not more than once, Ex:Baker and Ogletree these words are more then once so it is not include as Output

My Output like this

EmpID     EmpName           Company Name                            CompanyID
123        Josep       Kramer Levin Naftalis & Frankel LLP            468
123        Josep       Thompson Hine LLP                              567

Posted 21-Oct-15 4:11am

Member 12076430

Updated 21-Oct-15 4:18am

v2

Add a Solution

Comments

Duncan Edwards Jones 21-Oct-15 12:31pm

I would suggest you have a data quality issue if these are in fact the same company?

Member 12076430 21-Oct-15 13:20pm

no

phil.o 21-Oct-15 14:11pm

Wow, what a laconic precision ^^

[no name] 21-Oct-15 16:13pm

But short ...and... I Need at least for this answer no Google translate :lol:

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Answer 1 · 2015-10-23T02:00:00

Interestingly using an aggregate function and over clause -
a count can be performed on

SQL

LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)
--gets the first word of the company name as used in solution 1 (thanks Maciej Los)

Like so:

SQL

with Employee as (
--set up dummy data
	select 
		123 EmpID, 
		'Josep' EmpName, 
		'Kramer Levin Naftalis & Frankel LLP' CompanyName, 
		468 CompanyID
	union all select 123, 'Josep', 'Thompson Hine LLP', 567
	union all select 801, 'Simon', 'Ogletree Deakins International LLP', 222
	union all select 801, 'Simon', 'Ogletree, Deakins, Nash PC', 916
	union all select 602, 'alen', 'Baker Co Ltd', 732
	union all select 602, 'alen', 'Baker Mcken  Ltd', 242
)
select * from (
	select 
		*,
		count(*) over(partition by LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)) CompanyFirstWordCnt
	from Employee
) EmployeeCompanyFirstWordCnt
where CompanyFirstWordCnt = 1  --filter when count is 1
;

Maciej Los · Answer 2 · 2015-10-21T08:25:00

As Duncan Edwards Jones[^] mentioned in the comment to the question, you have to repair data in a CompanyName field.

At this moment, my best guess is:

SQL

DECLARE @tmp TABLE(EmpID INT, EmpName VARCHAR(50), CompanyName VARCHAR(155), CompanyID INT)

INSERT INTO @tmp(EmpID, EmpName, CompanyName, CompanyID)
VALUES(123, 'Josep', 'Kramer Levin Naftalis & Frankel LLP', 468),
(123, 'Josep', 'Thompson Hine LLP', 567),
(801, 'Simon', 'Ogletree Deakins International LLP', 222),
(801, 'Simon', 'Ogletree, Deakins, Nash PC', 916),
(602, 'alen', 'Baker Co Ltd', 732),
(602, 'alen', 'Baker Mcken  Ltd', 242)


SELECT EmpID, LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1) AS ShortComanyName
FROM @tmp
GROUP BY EmpID, LEFT(CompanyName, CHARINDEX(' ', REPLACE(CompanyName, ',', ''))-1)
HAVING COUNT(EmpID)=1

Above query returns:

EmpID	ShortComanyName
123		Kramer
123		Thompson

How to get exclude the name IF the name is more than once

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0