CODEWITHSUNDEEP
sql-servert-sql
LeonNewbie
LeonNewbie

Reputation: 41

SQL Comparing Data Keywords

Good morning,

I have been searching all over google for an answer to this so was wondering if anyone could help me out? Basically I have a database table, which consolidates keywords for example: dog, cat, horse, mouse. This is displayed as a result. However I need to create some SQL to say if two of those keywords exists, e.g horse and cat, then display these results. This would then allow us to go down X route.

Another example or issue which is a work around is to have a list of keywords, and then store this as a value, this would mean if someone typed in: my dog is violently attacking neighbours cats, it would detect dog and cat and would bring the user to a specific outcome..

declare @teststring varchar(512) = '{KEYWORD}' 
select top 1 k.type 
from (values 

('pet', '1'), ('dog', '1'), ('cat', '1')

) k(word,type) where @teststring like '%' + k.word + '%' 
group by k.type 
HAVING COUNT(1) >=2
order by COUNT(1) desc

As you can see this is storing cat, dog and pet. This then means if someone types my pet is a dog then it would go down a conditional route. (We then store the answers people put into {Keyword1}

So my issue here is I am limited to characters I can use in my SQL field. So I have created two lists (one list above with pet cat and dog.). Another list I have has exactly the same but (rabbit, hamster and horse). This is stored in {Keyword2} I want to be able to write some SQL that says if any of the keywords in {Keyword1} and {Keyword2} have been entered to then go down a specific conditional route. so if someone types by rabbit has been attacked by my dog it would detect rabbit and dog and would make this conditional and go down there.

I hope this isn't too confusing? I have thought about nested sql and inner joins but I am not sure this will do what im asking.

Upvotes: 2

Views: 142

Answers (2)

KtX2SkD
KtX2SkD

Reputation: 752

I'm not sure where's your problem, you seem to have it already covered, unless, you're having trouble making actionable code?

Maybe do this first:

  • Dispose of order by COUNT(1) desc,
  • Change >=2 to >=1,
  • Add your other keywords after cat with a different number, for example ('cat', '1'), ('keys', '2'), ('shirt', '2'),
  • Replace top 1 k.type with any placebo, like 1 or 'A',

Then, add this afterwards:

IF @@ROWCOUNT >= 2
    /* Do some stuff, because both keywords were used */

The concept is, if you wanna conditionally run some code, you'll usually need IF / ELSE, perhaps sometimes you can instead use TRY / CATCH or loops.

The @@ROWCOUNT isn't the mainstream way of checking in an IF, but it makes do many times, and I wanted to intervene on your code the least amount possible, for now. I might be even not getting you right to begin with.

The changes on your original query were aimed to have it produce an amount of records corresponding to how many keyword groups were found. Later on, that would be contested against @@ROWCOUNT as you have probably seen.

Hope I helped. All of this can be better done for sure, maybe when we can confirm what you're looking for.

Upvotes: 1

Kirky1
Kirky1

Reputation: 68

If you can store you keywords as tables you can use intersect:

DECLARE @table1 TABLE (KeyWord VARCHAR (100))
DECLARE @table2 TABLE (KeyWord VARCHAR (100))

    INSERT INTO @table1 
    VALUES 
    ('dog'),('rabbit'),('dragon'),('cat')

    INSERT INTO @table2
    VALUES 
    ('dog'),('rabbit'),('ogre'),('whale')

    SELECT KeyWord FROM @table1
    INTERSECT
    SELECT KeyWord FROM @table2

Upvotes: 0

Related Questions