Solved

SQL Query - WHERE column1 LIKE column2

Posted on 2009-05-20
3
628 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 68
Access Crosstab Query with Multiple Values 4 50
sql server concatenate fields 10 38
SQL - format decimal in a string 5 40
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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