sql - with - Use a LIKE clause in part of an INNER JOIN




sql like in (8)

If you are using a database that supports map-reduce, like couchdb, storing tags in a plain text field or list field is indeed the best way. Example:

tagcloud: {
  map: function(doc){ 
    for(tag in doc.tags){ 
      emit(doc.tags[tag],1) 
    }
  }
  reduce: function(keys,values){
    return values.length
  }
}

Running this with group=true will group the results by tag name, and even return a count of the number of times that tag was encountered. It's very similar to counting the occurrences of a word in text.

Can/Should I use a LIKE criteria as part of an INNER JOIN when building a stored procedure/query? I'm not sure I'm asking the right thing, so let me explain.

I'm creating a procedure that is going to take a list of keywords to be searched for in a column that contains text. If I was sitting at the console, I'd execute it as such:

SELECT Id, Name, Description
  FROM dbo.Card
 WHERE Description LIKE '%warrior%' 
       OR
       Description LIKE '%fiend%' 
       OR 
       Description LIKE '%damage%'

But a trick I picked up a little while go to do "strongly typed" list parsing in a stored procedure is to parse the list into a table variable/temporary table, converting it to the proper type and then doing an INNER JOIN against that table in my final result set. This works great when sending say a list of integer IDs to the procedure. I wind up having a final query that looks like this:

SELECT Id, Name, Description
  FROM dbo.Card
       INNER JOIN @tblExclusiveCard ON dbo.Card.Id = @tblExclusiveCard.CardId

I want to use this trick with a list of strings. But since I'm looking for a particular keyword, I am going to use the LIKE clause. So ideally I'm thinking I'd have my final query look like this:

SELECT Id, Name, Description
  FROM dbo.Card
       INNER JOIN @tblKeyword ON dbo.Card.Description LIKE '%' + @tblKeyword.Value + '%'

Is this possible/recommended?

Is there a better way to do something like this?


The reason I'm putting wildcards on both ends of the clause is because there are "archfiend", "beast-warrior", "direct-damage" and "battle-damage" terms that are used in the card texts.

I'm getting the impression that depending on the performance, I can either use the query I specified or use a full-text keyword search to accomplish the same task?

Other than having the server do a text index on the fields I want to text search, is there anything else I need to do?


It seems like you are looking for full-text search. Because you want to query a set of keywords against the card description and find any hits? Correct?


Try this

    select * from Table_1 a
    left join Table_2 b on b.type LIKE '%' + a.type + '%'

This practice is not ideal. Use with caution.


Your first query will work but will require a full table scan because any index on that column will be ignored. You will also have to do some dynamic SQL to generate all your LIKE clauses.

Try a full text search if your using SQL Server or check out one of the Lucene implementations. Joel talked about his success with it recently.


just use concatenation:

select a.*, b.Code,b.HHLDS
from Master1 as a left join map as b
on a.region like CAT("%",b.market,"%");

updated to use SAS concatenating function, and double quotes for string values.


try this:

Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like Concat('%',Table2.Name,'%') and Table1.Year=Table2.Year

in your query it will search for string containing Table2.Name (it is like constant)

as a suggestion joining on names is very very bad, what if you have 2 person with the same name??! So you need to have a primary and foreign key for this.


Recommended SQL database design for tags or tagging

Three tables (one for storing all items, one for all tags, and one for the relation between the two), properly indexed, with foreign keys set running on a proper database, should work well and scale properly.

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

“Like” operator in inner join in SQL

A bit of an odd data model aside, you've turned the tables around in the LIKE part (table1.name should be a part of table2.name, not the other way around), and you need to add the percents to the value, not the name of the field, that means not quoting the name;

SELECT table1.*, table2.z
FROM table1
INNER JOIN table2
  ON table2.name LIKE CONCAT('%', table1.name, '%') 
 AND table1.year = table2.year

An SQLfiddle to test with.





design-patterns