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
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.
select i.planname, c.planshortname
from invoice i, coverages c
where c.planshortname like i.planname
except the above doesn't work of course.