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.  
LVL 1
cciservicesAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
To fix you main issue, just add wildcards around it.  Actually should be short name, so should be like this.  I would also use ANSI compliant join.
select i.planname, c.planshortname
from invoice i
inner join coverages c on i.planname like '%' + c.planshortname + '%'

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:

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

Open in new window

0
 
cciservicesAuthor Commented:
Awesome. Thank you
0
All Courses

From novice to tech pro — start learning today.