Solved

SQL Query - WHERE column1 LIKE column2

Posted on 2009-05-20
3
615 Views
Last Modified: 2012-05-07
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
Comment
Question by:cciservices
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24434104

select i.planname, c.planshortname

from invoice i, coverages c 

where c.planshortname like '%' + i.planname + '%'

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24434130
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
 
LVL 1

Author Closing Comment

by:cciservices
ID: 31583605
Awesome. Thank you
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now