asked on

Using Like on 2 columns in a table

Hello Experts-

I am using the query below to pull data and trying to say where s.ProductSummary like k.courseTitle, but I am only getting one record returned. Can someone tell me how I can use Like to return all rows or if this needs to be written differently? As an example, within the DB, s.ProductSummary is stored as 'English 3D' and k.courseTitle is stored as 'English 3D: Customer Scenarios'

select s.ProductSummary
		,s.employeeID
        ,s.OwnerName
        ,s.SFDCOrderNumber
        ,k.personID
		,k.PersonFullName
        ,k.courseTitle
        ,case when k.courseTitle like s.ProductSummary then 1 else 0 end as relation
from pullsfdcdata s
left join pullkndata k on k.personID = s.employeeID
where s.ProductSummary like k.courseTitle

Open in new window

ste5an

The LIKE predicate requires patterns. You're feeding it a string without it, thus it is equivalent to

WHERE  s.ProductSummary = k.courseTitle;

Open in new window

For your desired result, as far as I understand you, you need

WHERE  s.ProductSummary LIKE '%' + k.courseTitle + '%';

Open in new window

But maybe you should rephrase your question, showing some data and the desired output.

nmarano

ASKER

Thank you Ste5an will try it out

nmarano

ASKER

It's throwing an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ k.courseTitle + '%'' at line 11"

select s.ProductSummary
,s.employeeID
,s.OwnerName
,s.SFDCOrderNumber
,k.personID
,k.PersonFullName
,k.courseTitle
from pullsfdcdata s
left join pullkndata k on k.personID = s.employeeID
WHERE s.ProductSummary LIKE '%' + k.courseTitle + '%'

nmarano

ASKER

Here is the data....

table.pullsfdcdata

SFDCOrderNumber ProductSummary employeeID
611511 Do the Math Now 55655
615455 English 3D 81569

table.pullsKNData

personID CourseTitle
55655 Do the Math 100
81569 English 3D: Customer Scenarios

Basically I am looking to check if a person who has a record in table.pullsfdcdata has taken a course from table.pullsKNData. Unfortunately I do not have a courseID that I can easily relate, so I am having to compare the ProductSummary Field with the CourseTitle field.

Hopefully this makes more sense.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmarano

ASKER

Thank you for the help ste5an

Zberteoc

Only part of the text in the 2 columns coincide. With like you would have that the value in one column to be entirely and the same part of the second column. These 2 texts will never be found by LIKE operator:

ProductSummary: Do the Math Now
CourseTitle: Do the Math 100

because neither

ProductSummary LIKE '%'+CourseTitle+'%'
nor
CourseTitle LIKE '%'+ProductSummary +'%'

will ever be TRUE!

ste5an

btw, you should really repair your data model..