| Author |
Topic |
|
BCullenward
Starting Member
28 Posts |
 Posted - 2005-07-11 : 16:46:37
|
| I need assistance with a query that will flag certain records that contain any number of words in one of the fields that will be found in another table with the word list.Table A has a field called Update_Info, Table B has a field called Word_List. I want to flag any records in Table A that have one of the words from the Word_List in the field Update_Info. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-07-11 : 17:34:50
|
Several ways to do this, some faster than others. Also, not sure how you want to "flag"... do you mean set a flag in the record, or return only those records that match?Sample data would help. Does Update_Info hold a single word, or a string of words? I'll assume Update_Info holds a string of words.UPDATE TA SET Flag = 1 FROM TableA TA CROSS JOIN TableB TB WHERE TA.Update_Info LIKE '%' + TB.Word_List + '%' There may be better execution plans if a solution is found that doesn't use a CROSS JOIN. |
 |
|
|
BCullenward
Starting Member
28 Posts |
Posted - 2005-07-11 : 17:41:03
|
| I just want those records that contain a word from the wordlist to be returned. I will play with your suggestion and see if it works for me. Thanks. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-07-11 : 18:10:37
|
quote:
Originally posted by BCullenward
I just want those records that contain a word from the wordlist to be returned.
Flag can mean different things.Try thisSELECT TA.* -- I never use *, but you don't mention the other columns FROM TableA TA WHERE EXISTS (SELECT 1 FROM TableB TB WHERE TA.Update_Info LIKE '%' + TB.Word_List + '%') |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-07-11 : 18:17:34
|
If Update_Info has only one word, then you don't need pattern matching...SELECT TA.* FROM TableA TA INNER JOIN TableB TB ON TB.Word_List = TA.Update_Info |
|
|
|
BCullenward
Starting Member
28 Posts |
Posted - 2005-07-12 : 09:32:53
|
| Hrmmm... That didn't work for me. In Update_Info there are several words as the user types in what the updated status is and any of them that contain a word that is found in the wordlist table need to be returned. When I used the first two that you gave me, it returned all the records (even those that did not contain a word from the word list) |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-12 : 09:43:00
|
| Look in BOL for "CONTAINS" and "FREETEXT". Not sure if this is the right direction or not, but it cant hurt to look.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-12 : 10:13:37
|
| >>In Update_Info there are several words as the user types in what the updated status isWhy is this freeform? If this status value is important, you should restrict it to a set list of values. YOu can always provide an additional "notes" or "more info" column for any extra text needed. For meaningful data, precise reporting, and easier/shorter/faster SQL queries, you should define a set of status codes that cover the possibilites you need to handle.- Jeff |
|
|
|
BCullenward
Starting Member
28 Posts |
Posted - 2005-07-12 : 10:39:44
|
| It's freeform because it is based on the conversation the user has had with a client. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-12 : 10:51:52
|
| That is a not a reason to make it freeform ... Did you get a chance to read all of my post?- Jeff |
|
|
|
BCullenward
Starting Member
28 Posts |
Posted - 2005-07-12 : 12:49:40
|
| I did read your whole post, however, I'm not the DBA, so I really have no say in the design of the database. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-12 : 14:18:26
|
| Let your DBA know the database as structured isn't meeting your needs. If there's any debate, be sure to make sure to let the DBA know that you are being forced with write SQL statements that cannot use indexes and will incurr table scans. If your DBA is qualified to do his or her job, they will understand that this is a very bad thing.For more solutions to your current problem if you cannot have the database restructured properly, post your DDL and some sample data and we'll try to help.- Jeff |
|
|
|
|
Word List Query