CODEWITHSUNDEEP
sql-servert-sqljoin
Volearix
Volearix

Reputation: 1593

Check sql table for values in another table

If I have the following data:

Results Table

.[Required]
I want one grape
I want one orange
I want one apple
I want one carrot
I want one watermelon

Fruit Table

.[Name]
grape
orange
apple

What I want to do is essentially say give me all results where users are looking for a fruit. This is all just example, I am looking at a table with roughly 1 million records and a string field of 4000+ characters. I am expecting a somewhat slow result and I know that the table could DEFINITELY be structured better, but I have no control of that. Here is the query I would essentially have, but it doesn't seem to do what I want. It gives every record. And yes, [#Fruit] is a temp table.

SELECT * FROM  [Results]
JOIN [#Fruit] ON
'%'+[Results].[Required]+'%' LIKE [#Fruit].[Name]

Ideally my output should be the following 3 rows:

I want one grape
I want one orange
I want one apple

Upvotes: 0

Views: 44

Answers (3)

Andrew Yoo
Andrew Yoo

Reputation: 46

This topic interests me, so I did a little bit of searching.

Suggestion 1 : Full Text Search

I think what you are trying to do is Full Text Search .

You will need Full-Text Index created on the table if it is not already there. ( Create FULLTEXT Index ).

This should be faster than performing "Like".

Suggestion 2 : Meta Data Search

Another approach I'd take is to create meta data table, and maintain the information myself when the [Result].Required values are updated(or created).

Upvotes: 1

iDevlop
iDevlop

Reputation: 25252

If that kind of think is doable, I would try the other way round: 

SELECT * FROM  [Results]
JOIN [#Fruit] ON
[Results].[Required] LIKE '%'+[#Fruit].[Name]+'%'

Upvotes: 1

Topher Hunt
Topher Hunt

Reputation: 4804

This looks more or less doable, but I'd start from the Fruit table just for conceptual clarity.

Here's roughly how I would structure this, ignoring all performance / speed / normalization issues (note also that I've switched around the variables in the LIKE comparison):

SELECT f.name, r.required
FROM fruits f
JOIN results r ON r.required LIKE CONCAT('%', f.name, '%')

...and perhaps add a LIMIT 10 to keep the query from wasting time while you're testing it out.

This structure will:

  • give you one record per "match" (per Result row that matches a Fruit)
  • exclude Result rows that don't have a Fruit
  • probably be ungodly slow.

Good luck!

Upvotes: 0

Related Questions