Query Where Column is Like Value From Field Equals Value Field2

Question

4.33/5 (3 votes)

See more:

SQL-Server-2008

I am currently working on a project that requires us to query on various codes and extract/submit the data following industry standards.

I created a cross reference table that stores the source values that will be found in the operational system mapped to the target value for submission.

In some cases we have to perform a LIKE statement on the source field because we will not always know the exact value and there may be several combinations with the same meaning that we want to include. For example, we want all the records with the word "science". I want to include "science 1", "science 2", "science summer school", etc. and they will all be mapped (see below) to a TargetValue of "General".

Table Mapping – Cross Reference Table
MapName	SourceValue	TargetValue
Class_Taken	Science	General
Class_Taken	1345	General
Class_Taken	Math	General
Class_Taken	Reading	General
Class_Taken	23041	General

Table StudentCourses – Source Table
ID	CourseDate	CourseName
111111	01/10/2010	Algebra
311112	01/10/2011	Science 101
131211	01/10/2009	English
112111	04/01/2010	Math Basics
121311	08/15/2010	Science
211131	01/10/2010	Reading

This is what I am hoping to do to keep my query generic and only have one query to avoid having to join to the same tables 15 times since there are many different types of mappings we have to perform. I want to get the results using a LIKE comparison where the StudentCourses.CourseName is like the Mapping.SourceValue value (want to avoid hard coding the actual LIKE condition because then we would have to write a query for every single rule) then if I get a match set the CourseName value to the correlating TargetValue so I know to include it in that group.

SQL

SELECT
    *
FROM StudentCourses 
INNER JOIN Mapping Map 
    ON
        CASE
            WHEN '%StudentCourses.CourseName%' LIKE 'Map.Source_Value' THEN 'Map.Target_Value'
            ELSE null
        END = Map.Target_Value

This is where I am getting stuck; I don't know how to perform a LIKE statement on the CourseName field.

And I'm wondering if the rest would work and/or any recommendations of a better way.

Posted 16-Jun-11 10:10am

Member 7993517

Updated 16-Jun-11 11:41am

AspDotNetDev

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AspDotNetDev · Answer 1 · 2011-06-16T11:53:00

Your question isn't very clear to me (would help if you showed the output you were after), but this seems like what you are after:

SQL

-- Sample data.
DECLARE @Mapping table (MapName varchar(100), SourceValue varchar(100), TargetValue varchar(100))
DECLARE @StudentCourses table (ID int, CourseDate datetime, CourseName varchar(100))
INSERT INTO @Mapping VALUES('Class_Taken', 'Science', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', '1345', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', 'Math', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', 'Reading', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', '23041', 'General')
INSERT INTO @StudentCourses VALUES (111111, GetDate(), 'Algebra')
INSERT INTO @StudentCourses VALUES (311112, GetDate(), 'Science 101')
INSERT INTO @StudentCourses VALUES (131211, GetDate(), 'English')
INSERT INTO @StudentCourses VALUES (112111, GetDate(), 'Math Basics')
INSERT INTO @StudentCourses VALUES (121311, GetDate(), 'Science')
INSERT INTO @StudentCourses VALUES (211131, GetDate(), 'Reading')

-- Query.
SELECT
	ID,
	CourseDate,
	CASE
		WHEN TargetValue IS NULL THEN CourseName
		ELSE TargetValue
	END AS NewCourseName
FROM @StudentCourses AS Courses
LEFT JOIN @Mapping AS Map
	ON Courses.CourseName LIKE '%' + Map.SourceValue + '%'

/* Output:

111111	2011-06-16 14:50:21.233	Algebra
311112	2011-06-16 14:50:21.233	General
131211	2011-06-16 14:50:21.233	English
112111	2011-06-16 14:50:21.233	General
121311	2011-06-16 14:50:21.233	General
211131	2011-06-16 14:50:21.233	General

*/