Seemingly Simple SQL Question

[l3stuff]

Dear all

I have two table, containing company info, and company employees, let's say (tables) 'Comp_list' and 'Comp_Emp'

In 'Comp_list' we have a field called 'Comp_list.Comp_name' and in 'Comp_Emp' we have a field 'Comp_Emp.Employer'

I want my query to just return those companys in 'Comp_Emp.Employer' where the company name equals or is like the comany name held in 'Comp_list.Comp_name'

I've tried various permutations of joins but the query never returns what I expect...

Any/all help greatly appreciated..

/pah

 

[swampBoogie]

select * from comp_list
where comp_name in (select employer from comp_emp)

 

[JamesLean]

SELECT *
FROM comp_list t1 JOIN comp_emp t2 ON t1.comp_name = t2.employer

This will find employees which have an exact matching company name in the company list.

You may have more difficulty finding names which "are like" each other. You could start by testing just the first few letters matching, eg:

SELECT *
FROM comp_list t1 JOIN comp_emp t2 ON LEFT(t1.comp_name, 5) = LEFT(t2.employer, 5)

--James

 

[JohnDTampaBay]

How does this work for you? It all really depends on exactly what you mean by "Comp_Emp.Employer like Comp_List.Comp_Name". This example will return the employer IBM twice if there were the company names IBM Corp. and IBM. It will also return another row if there were the company NotIBMCorp Inc..

SELECT e.Employer FROM Comp_Emp e, CompList l
WHERE e.Employer LIKE '%' + l.Comp_Name + '%'

Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.