I have a table with contact information including company name.
I am trying to find company names that are a soft match for a company already on file - for companies that are entered as XXXXXX LTD. or Limited or LP etc.
I have a query that finds all of the company names that are unique but I would like to expand the query so that I then match those names on the first x number of characters to names in the table - the ones that I assume could be a soft match and possibly why they are unique.
Here is my query:
select c1.accountno, c1.company from contact1 c1
where (c1.company not in (select c12.company from contact1 c12 where c1.company = c12.company and c1.accountno<> c12.accountno))
and c1.company <> ''
order by c1.company
this gives me all of the company names that have only one record on file
I then try to expand the query to:
select c1.accountno, c1.company from contact1 c1
where (c1.company not in (select c12.company from contact1 c12 where c1.company = c12.company and c1.accountno<> c12.accountno)
and c1.company in (select c13.company from contact1 c13 where left(c1.company,5) = left(c13.company,5)
and c1.accountno <> c13.accountno))
and c1.company <> ''
order by c1.company
which I thought would give me all of the the unique company names that match a company name on the first five characters but not match on itself. I know that there are records in the table that meet the conditions but I get no records at all from this query.
when I remove the condition: and c1.accountno <> c13.accountno I get the same records as the first query.
Am I missing something? is there a way to do what I want to do?
I am not that versed in sql but have been plugging away at it for a while now.
Thanks for any suggestsions that you can offer.
Kim
I am trying to find company names that are a soft match for a company already on file - for companies that are entered as XXXXXX LTD. or Limited or LP etc.
I have a query that finds all of the company names that are unique but I would like to expand the query so that I then match those names on the first x number of characters to names in the table - the ones that I assume could be a soft match and possibly why they are unique.
Here is my query:
select c1.accountno, c1.company from contact1 c1
where (c1.company not in (select c12.company from contact1 c12 where c1.company = c12.company and c1.accountno<> c12.accountno))
and c1.company <> ''
order by c1.company
this gives me all of the company names that have only one record on file
I then try to expand the query to:
select c1.accountno, c1.company from contact1 c1
where (c1.company not in (select c12.company from contact1 c12 where c1.company = c12.company and c1.accountno<> c12.accountno)
and c1.company in (select c13.company from contact1 c13 where left(c1.company,5) = left(c13.company,5)
and c1.accountno <> c13.accountno))
and c1.company <> ''
order by c1.company
which I thought would give me all of the the unique company names that match a company name on the first five characters but not match on itself. I know that there are records in the table that meet the conditions but I get no records at all from this query.
when I remove the condition: and c1.accountno <> c13.accountno I get the same records as the first query.
Am I missing something? is there a way to do what I want to do?
I am not that versed in sql but have been plugging away at it for a while now.
Thanks for any suggestsions that you can offer.
Kim