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 |
|
ljp099
Yak Posting Veteran
79 Posts |
 Posted - 2007-11-13 : 18:19:51
|
| I have a select statment:select content_id, content_html from content where content.folder_id=30 AND CHARINDEX('<Author>Lance Parish</Author>', content_html) > 1 AND CHARINDEX('<Webinar>Presentation</Webinar>', content_html)This query returns 29 records.I then want to modify the query to add to the where clause, which should return less than 29 records:select content_id, content_html from content where content.folder_id=30 AND CHARINDEX('<Author>Lance</Author>', content_html) > 1 AND CHARINDEX('<Webinar>Presentation</Webinar>', content_html) > 1 AND CHARINDEX('<SubjectArea>Content Development</SubjectArea>', content_html) > 1 OR CHARINDEX('<SubjectArea>Learning Programs</SubjectArea>', content_html) > 1The issue is with the OR clause. It does not limit the recordset but expands it. Do I need to group the Where clause with the AND clause that precedes it, in order to get a more limited recordset?Thanks for any help |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-13 : 18:25:41
|
select content_id, content_html from content where (content.folder_id=30 AND CHARINDEX('<Author>Lance</Author>', content_html) > 1 AND CHARINDEX('<Webinar>Presentation</Webinar>', content_html) > 1 AND CHARINDEX('<SubjectArea>Content Development</SubjectArea>', content_html) > 1 ) OR CHARINDEX('<SubjectArea>Learning Programs</SubjectArea>', content_html) > 1THe bolded part should be parenthetically enclosed as I have done. That way it is (condition1) OR (Condition2)Where condition 1 is your series of AND requirements above.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
ljp099
Yak Posting Veteran
79 Posts |
Posted - 2007-11-13 : 20:54:32
|
| Am I missing a close parenthesis? I only see that you have added the parenthesis to the beginning of the where clause:select content_id, content_html from content where (The rest of the statement looks identical.Thanks for your help. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-13 : 21:38:42
|
Did you try what I typed? I added one at the end as well. I wrapped the conditionals before the OR in a () and left the rest unchanged.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
ljp099
Yak Posting Veteran
79 Posts |
Posted - 2007-11-14 : 10:54:09
|
| Ok. Yes I added both parenthesis. The query comes back with the same number of records as when I run the query without the parenthesis wrapped around the AND clauses:select content_id, content_html from content where (content.folder_id=30 AND CHARINDEX('<Author>Lance Parish</Author>', content_html) > 1 AND CHARINDEX('<Webinar>Presentation</Webinar>', content_html) > 1 AND CHARINDEX('<SubjectArea>Content Development</SubjectArea>', content_html) > 1) OR CHARINDEX('<SubjectArea>Learning Programs</SubjectArea>', content_html) > 1 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-14 : 11:03:28
|
[code]select content_id, content_htmlfrom contentwhere content.folder_id = 30 AND CHARINDEX('<Author>Lance</Author>', content_html) > 1 AND ( CHARINDEX('<SubjectArea>Content Development</SubjectArea>', content_html) > 1 OR CHARINDEX('<SubjectArea>Learning Programs</SubjectArea>', content_html) > 1 ) AND CHARINDEX('<Webinar>Presentation</Webinar>', content_html) > 1[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
ljp099
Yak Posting Veteran
79 Posts |
Posted - 2007-11-14 : 11:31:55
|
| That did it! Thanks for your help. Now all I have to do is translate this SQL query into Xpath. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-14 : 11:35:54
|
Good luck.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
Help with Select Statment