Link to home
Start Free TrialLog in
Avatar of cciservices
cciservicesFlag for United States of America

asked on

SQL Query - WHERE column1 LIKE column2

I am working on a query that doesn't have a traditional join criteria and I am struggling how to return the results I want. This is part of a larger query, but the part I need help with is this.
Table Invoice has a column called planname with data such as:
00000701 PLAN V-E PPP VIS (Rockaway)
00000903 PLAN V-A VIS NO PE NO WBC (Astoria)

Table Coverages has a column called planshortname and has data such as:
PLAN V-E PPP
PLAN V-A
VIS
NO PE

I need to write something look at the invoice table and look within the planshortname column and join it with the coverages table where the planshortname contains a value in the coverages table.
Something like
select i.planname, c.planshortname
from invoice i, coverages c
where c.planshortname like i.planname

except the above doesn't work of course.  
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image


select i.planname, c.planshortname
from invoice i, coverages c 
where c.planshortname like '%' + i.planname + '%'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cciservices

ASKER

Awesome. Thank you