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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-16 : 08:21:33
|
King writes "I have a search field on the home page that I want to allow users to enter a value and search by different criteria in the same table, "Title", "Keywords", or "Oneliner". But I also need to know the record is "active" (active=1) AND be able to return accurate results for AccessType.
There are two types of access: Everyone and Executives. Normal users can only view submissions that are not assigned to "Executives" AccessType and Executives can view all submissions.
The sample SQL statement below returns "Executive" and "Everyone" rows using "OR". If I substitute "OR" with "AND" it returns only the row(s) where the search value is contained in Title and keywords and oneliner, not either.
Can you shove me in the right direction so I can return any and all rows that have, let's say, "score" in either the "Title", the "Keywords" or the "Oneliner" columns and AccessType <> "Executives"?
Any help would be appreciated.
Thanks,
King Wilder
king@gizmobeach.com
SELECT UNames.UNamesID, UNames.FirstName,
UNames.LastName, LitSubmissions.SubmissionID,
LitSubmissions.Title, LitSubmissions.Keywords,
LitSubmissions.Active, LitSubmissions.OneLiner,
LitSubmissions.AccessType, LitSubmissions.Date
FROM UNames INNER JOIN
LitSubmissionJoin ON
UNames.UNamesID = LitSubmissionJoin.UNamesID INNER JOIN
LitSubmissions ON
LitSubmissionJoin.SubmissionID = LitSubmissions.SubmissionID
WHERE (LitSubmissions.OneLiner LIKE '%score%') or
(LitSubmissions.Keywords LIKE '%score%') or
(LitSubmissions.Title LIKE '%score%') AND
(LitSubmissions.Active = 1) AND
(LitSubmissions.AccessType <> 'Executives')" |
|
|
|
|
|
|
|
Conditional search...