Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

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.  
0
cciservices
Asked:
cciservices
  • 2
1 Solution
 
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
 
Kevin CrossChief 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
 
cciservicesAuthor Commented:
Awesome. Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now