Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ZoneFX
Starting Member
17 Posts |
 Posted - 2009-12-14 : 14:31:05
|
| I want to select documents from my table that fill a certain criteria:I’d like a Where clause that will do this for me:Basically, I want to find my search box text in the Title or the Description column but I don’t want to return any results where the Expiry date has passed. Except if the record is marked as 1 in the Category1 column? This is what I’ve got so far and it doesn’t work. It still returns records that have expired that aren’t marked as 1 in Category1Where Title like @SearchText OR [Description] like @SearchText and (Expiry < GetDate() and Category1 <> 1) |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-12-14 : 14:47:29
|
| Where (Title like @SearchText OR [Description] like @SearchText) and (Expiry < GetDate() and Category1 <> 1)Mike"oh, that monkey is going to pay" |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ZoneFX
Starting Member
17 Posts |
Posted - 2009-12-14 : 16:47:55
|
| It's part of a stored procedure :@SearchText varchar(50),Not sure I understand your comments X002548. I'm an occasional user of SQL and use these forum for guidance, typically I get a solution to my problem amazingly quickly. Sorry if my lack of knowledge confuses you. Just trying to improve my knowledge and work out what to do with those pesky brackets!! Mike - Thanks for your help! |
|
|
|
ZoneFX
Starting Member
17 Posts |
Posted - 2009-12-14 : 17:22:07
|
Unfortunately this doesn't workWhere (Title like @SearchText OR [Description] like @SearchText) and (Expiry > GetDate() and Category1 <> 1) This will return all records that haven't expired that meet the search criteria, which is great, but I'd also like to grab all the records that have a 1 in the column Catergory1, regardless of if they're expired or not. As well as all the other non-expired records. If a record has a number other than 1 in Category1 and has expired. I don't want it to be returned.Appologies for my ignorance. I wish I could express what I need as clearly as it is in my head!! |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-14 : 17:49:07
|
Change the 'and' to 'OR'..and <> to =Where (Title like @SearchText OR [Description] like @SearchText) and (Expiry > GetDate() OR Category1 = 1) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ZoneFX
Starting Member
17 Posts |
Posted - 2009-12-15 : 04:07:55
|
| Thank you vijayisonly - This works perfectly!! Brett- I still don't understand your comments? I don't know what a scan is, but if it's any help for you to help me. I'm grabbing the content of a user input text box - I append a % to either side of this and that becomes the @SearchText. It's not ideal, but it works for what I need. I'm about to change this to split each word within the search text and then search for all words in the user input. Still working out how to do this though! |
|
|
|
|
|
|
Where clause help