Return Employee name in unmatched companies

Question

question

thennarasu asked Oct 21, '15

Return Employee name in unmatched companies

Here is my Request, if the first word of the first company matches the first word of the second company it does not return. For example emprid 11 won’t be returned because both of the companies have Baker at the start of the name.

Empid	 Name	        Company	          Companyid
  11	 Alberto    Baker & Mec Ltd  	  25
  11	 Alberto    Baker & rel LLP 	  26

Whereas empid 12 will be returned because the first word differs between the two companies.

Empid	 Name	        Company	          Companyid
  12	 Ameez     Baker & Mec Ltd  	   25
  12	 Ameez     Wrong & Part LLP	       27

--- **Edit:** Pulled up from a clarifying answer below --- i will repeat my question clearly 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   123         
123        Josep       Thompson Hine LLP                      123

sql

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Oct 21, 2015 at 12:07 PM

Hi. Is this homework? Because, going by your previous questions, it feels like it...

0 ·

thennarasu commented · Oct 21, 2015 at 01:32 PM

No it was asked by client

0 ·

ThomasRushton ♦♦ commented · Oct 21, 2015 at 02:37 PM

So what have you managed so far? Where are you stuck?

0 ·

thennarasu commented · Oct 21, 2015 at 03:03 PM

i have tried this but no result First created temp table #dir and then executed this query select #dir.* from (select Substring( [Company Name], 1, CharIndex( ' ', [Company Name] ) - 1) as minfirstword ,Substring( [Company Name], 1, CharIndex( ' ', [Company Name] ) - 1) as maxfirstword from #dir ) #dir where minfirstword <> maxfirstword

0 ·

thennarasu commented · Oct 21, 2015 at 05:35 PM

for your understanding i will repeat my question clearly 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   123         
123        Josep       Thompson Hine LLP                      123

0 ·

Show more comments

Answer 1 · 2015-10-21T16:14:32Z

Tom Staab answered Oct 21, '15

I believe this will work for you, but you could run into performance issues. It will return each company for each employee unless that company name beginning is the same as another company for the same employee. In that case, neither company will return. I'm not sure if that is actually what you want, but that's the way I interpreted your request. Please let us know if that's not right. Also, I see a potential error and a performance concern with this solution, but I'll explain that more after the SQL. SELECT * FROM #dir d1 WHERE NOT EXISTS ( -- 2 different companies with names starting with the same word SELECT 1 FROM #dir d2 WHERE d2.EmpId = d1.EmpId AND d2.CompanyId != d1.CompanyId AND SUBSTRING(d2.[Company Name], 1, CHARINDEX(' ', d2.[Company Name])) = SUBSTRING(d1.[Company Name], 1, CHARINDEX(' ', d1.[Company Name])) ) ORDER BY EmpId, [Company Name] ; The potential error would occur if a company name has no spaces (i.e. is only 1 word). To address that, I recommend testing the CHARINDEX. My performance concern is with the comparison using SUBSTRING. If this table has a lot of rows, it might help to index a computed column for the first word of the company name. Here is an example of how to do that (and including the appropriate test to avoid the error I mentioned earlier). ALTER TABLE #dir ADD CompanyFirstWord AS ( CASE WHEN CHARINDEX(' ', [Company Name]) is null THEN [Company Name] WHEN CHARINDEX(' ', [Company Name]) = 0 THEN [Company Name] ELSE SUBSTRING([Company Name], 1, CHARINDEX(' ', [Company Name]) - 1) END ); CREATE INDEX [#dir_CompanyFirstWord_IX] ON #dir (CompanyFirstWord);

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Oct 21, 2015 at 05:41 PM

Just a thought - it might be advantageous to filter that inner SELECT a bit more... WHERE d2.empid = d1.empid AND d2.companyID d1.companyid Assuming, that is, that there's a UNIQUE constraint on the combination of EmpID & COmpanyID. If there isn't, then perhaps checking the company names don't match? Assuming, that is, that the application / database is designed to prevent duplicates. OK, you can stop laughing at the back... ;-)

1 ·

Tom Staab ♦ commented · Oct 21, 2015 at 06:08 PM

Thanks, Thomas. I added the condition that company IDs not match because I think it's more than just advantageous. After considering your comment, I think it's actually required. Without that, no records would be returned because every company would match on itself.

0 ·

ThomasRushton ♦♦ commented · Oct 21, 2015 at 06:31 PM

Oh, hang on. Misparsed your query. Going to have to think about it.

0 ·

thennarasu commented · Oct 22, 2015 at 08:24 AM

for your understanding i will repeat my question clearly 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

EmpID     EmpName           Company Name                    companyid                            
123        Josep       Kramer Levin Naftalis & Frankel LLP   123         
123        Josep       Thompson Hine LLP                      123

0 ·

Answer 2 · 2015-10-21T19:00:25Z

ThomasRushton answered Oct 21, '15

Here's my attempt: So here we're just setting up our table of data DECLARE @EmpTab TABLE ( EmpID INT , EmpName VARCHAR(10) , CompanyName VARCHAR(50) , CompanyID INT ); INSERT INTO @EmpTab 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 ); Here's the first attempt - which I did before realising that you had that "Ogletree, Deakins" and "Ogletree Deakins" bit, which caused the query to incorrectly return those two records: SELECT * FROM @EmpTab a INNER JOIN @EmpTab b ON a.EmpID = b.EmpID AND a.CompanyID b.CompanyID AND SUBSTRING(a.CompanyName, 0, CHARINDEX(' ', a.CompanyName)) SUBSTRING(b.CompanyName, 0, CHARINDEX(' ', b.CompanyName)); So here's a slightly more complex version of the query. Using a `WITH` clause to build a CTE with the FirstWord field. This is built as follows: * In order to get round the potentiality of a single-word company, we're adding a space to the end of the company name * then we're returning everything to the left of the first space * then we're replacing commas and full stops with blanks (no, not spaces...) You might want to add some extra REPLACE clauses, depending on your requirements (apostrophes? Ampersands? Hyphens?) WITH EmpTabFirstWord AS ( SELECT EmpID , EmpName , CompanyName , CompanyID , REPLACE(REPLACE(SUBSTRING(a.CompanyName, 0, CHARINDEX(' ', a.CompanyName + ' ')), ',', ''), '.', '') AS FirstWord FROM @EmpTab a ) SELECT * FROM EmpTabFirstWord a INNER JOIN EmpTabFirstWord b ON a.EmpID = b.EmpID AND a.CompanyID b.CompanyID AND a.FirstWord b.FirstWord Here's @Tom Staab's query adjusted to fit my data, for comparison purposes SELECT * FROM @EmpTab d1 WHERE NOT EXISTS ( SELECT 1 FROM @EmpTab d2 WHERE d2.EmpId = d1.EmpId AND d2.CompanyId != d1.CompanyId AND SUBSTRING(d2.[CompanyName], 1, CHARINDEX(' ', d2.[CompanyName])) = SUBSTRING(d1.[CompanyName], 1, CHARINDEX(' ', d1.[CompanyName])) ) ORDER BY EmpId , [CompanyName]; As written, Tom's query is rather more efficient than my WITH-based effort; however, I'm doing the two REPLACEs that he's not doing. So the comparison isn't entirely valid. You want some small print? This is potentially woefully inefficient in terms of processing; and you've got potential for a horribly mangled set of reference data keeping the company ID & name in the same table as employee data - these should be separated, particularly if there's a many-to-many relationship between employees and companies.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question