CODEWITHSUNDEEP
sqlsql-server
noor
noor

Reputation: 122

SQL-searching through records to find the match of the word present in another table

I have two tables Client_Type and Client_Data

     Client_Type                             Client_Data
  Type    Description                ID   Type      Files
  -------------------               -----------------------------------
   A       AGREEMENT                 1   Student  PDF-Report Card.pdf
   E       EXAM PAPER                2   Teacher  PDF- Contract Agreement.pdf
   L       LETTER                    3   Student  word- Final exam paper.doc
   R       REPORT                    4   Student  PDF-Letter.pdf

I want to make a third table based of above two tables something like 

 ID   Client_Type.Type       Files
 ----------------------------------- 
  1      R                PDF-Report Card.pdf
  3      E                word- Final exam paper.doc
  4      L                PDF-Letter.pdf

I want to search through [Client_Data].[Type] and if it is Student then I want to match the [Client_Type].[Description] with the [Client_Data].[Files], If there is a word that matches the description I want to assign the type to that record in third table

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You want something like this:

SELECT cd.*, ct.type AS client_type
FROM client_data cd JOIN
     client_type ct
     ON cd.files LIKE '%' + ct.description + '%'
WHERE cd.type = 'Student';

If you have a case-sensitive collation, you can lowercase the columns for the ON clause:

     on lower(cd.files) like '%' + LOWER(ct.description) + '%'

And, if to be safe, you want to keep all students, then use a LEFT JOIN rather than just JOIN.

Upvotes: 1

JNevill
JNevill

Reputation: 50308

This probably won't be fast (Depending on the size of your data) but it should do what you want:

SELECT
    cd.id,
    ct.type,
    cd.files
FROM
    client_data cd
    INNER JOIN client_type ct
        ON cd.Files LIKE '%' + ct.description + '%'
WHERE
    cd.Type = 'Student';

Upvotes: 1

Related Questions