CODEWITHSUNDEEP
sqlsql-serverjoin
Learning
Learning

Reputation: 20021

How to get matching result with Joins using Like operator

I have two table one which is master table & other is temporary table to store keywords i want keywords/tags in the tempTable to match the records in master table and show relevant result below is the sample structure on SQL Fiddle 

CREATE TABLE [TestTable](
    [AutoID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NULL,
    [Category] [nvarchar](50) NULL,
    [Tags] [nvarchar](200) NULL,
    Country [nvarchar](50)
) 




INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags] ,[Country]) VALUES('This is News One America','Politics','Donald Trump, Iran', 'US')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News  Two Korea','Economics','North Korea, Donald Trump, Kim Jong','North Korea')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News  Three  Human Rights','Human Rights','Suu Kyi, Myanmar, Rohingya','Myanmar')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News  Four Syria','Conflicts','Syria, Russia, America, Turkey','Syria')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News  Six  Election','Politics','US, Russia, election, Donald Trump','US')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News  Seven Technology','Technology','Amazon, Alexa','US')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News Eight Climate','Climate','weather, europe, climate change','UK')
INSERT INTO [TestTable] ([Title] ,[Category] ,[Tags],[Country]) VALUES('This is News five America -iran','Politics','Donald Trump, Iran','Iran')

CREATE TABLE [Tags](
    [Tags] [nvarchar](200) NULL,
)
INSERT INTO [Tags] VALUES('Donald Trump')
INSERT INTO [Tags] VALUES('iran')

I want tags from TAGS Table to match tags column in TestTable, testTable tags can have single on multiple tags in one column such as

Upvotes: 1

Views: 44

Answers (2)

Ashley Kurkowski
Ashley Kurkowski

Reputation: 206

The following is a naive solution to the problem

SELECT     *
FROM       Tags t
INNER JOIN TestTable TT on tt.Tags like '%'+t.Tags+'%'

You could theoretically end up with some false positives using the above approach if your tag was a single word and your test tag was multiple words.

For example, if your test tags had a tag called "donald trump" and your tag was "donald", this approach would incorrectly match the result.

Another approach is to split your string delimetered column TestTable.Tags and then join to the resulting value to your tags table to get you an exact match.

SELECT      *
FROM        TestTable tt
CROSS APPlY (
  SELECT LTRIM(RTRIM(Value)) as Tag FROM  STRING_SPLIT(tt.Tags, ',') 
) SS
INNER JOIN  Tags t on t.Tags = ss.Tag

LTRIM and RTRIM remove whitespace from the left and right side of the string.

Documentation for string split is here: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Documentaion for LTRIM here: https://learn.microsoft.com/en-us/sql/t-sql/functions/ltrim-transact-sql

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

This is too long for a comment.

Fix your data structure! You need a junction table, with one row per "auto" and one per "tag".

CREATE TABLE [Tags] (
    TagId INT IDENTITY(1, 1) PRIMARY KEY,
    Tags [nvarchar](200) NULL
);

CREATE TABLE AutoTags (
    AutoTagID int IDENTITY(1, 1) PRIMARY KEY,
    AutoId INT NOT NULL,
    TagId INT NOT NULL,
    CONSTRAINT fk_autotags_autoid FOREIGN KEY (AutoId) REFERENCES testtable(autoid),
    CONSTRAINT fk_autotags_tagid FOREIGN KEY (TagId) REFERENCES tags(tagid)
) ;

Once you have this structure, the database will guarantee that the tags are correct. Most operations on finding tags will be simpler and more efficient.

Upvotes: 1

Related Questions