Multiple "AND" and "OR" Statements in an SQL Query


 I've got a table called "plants" which, up-until-recently, was being searched by category using the following SQL (and using the CategoryID field):

SELECT plants.Latin_Name, Size.ekm_ID, plants.Common_Name, plants.Short_Description, plants.Thumb_URL, plants.CategoryID, plants.ProductID, Size.Size, Size.Price, Size.MostPopular FROM plants INNER JOIN Size ON plants.CategoryID = Size.CategoryID AND plants.ProductID = Size.ProductID WHERE (plants.CategoryID = @CategoryID) AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name

 I now want to offer users two ways of searching for the plant they want - I need to add another set of categories which plants can be searched by. This is what I mean...

 Category Set One (Traditional Categories) - Aquatics, Acers, Climbers, Conifers, Exotics, Fruit, Grasses/Bamboo, Heathers, Hedging, Herbs, Perennials, Rhododendrons, Roses, Shrubs, Topiary

 Category Set Two (More Recent Way of Grouping Plants) - Roses that grow on you, hedge your bets, sun & shady characters, social climbers, shady characters, sun shine boys, the fruitful good life, herbs 'what you got cooking, sensory sensations, silent whispoers, architectural plants, wildlife lovers, rockery rockers, trees for rockery & patio, trees for gardens, trees for smallholdings & farms, perennial penchant, groundcover groundhogs

Every plant we sell appears in 1 category from category set one and one or two categories from category set 2.

The relevant fields in the plant table are: CategoryID (traditional categories), NewCategoryID1 (1st category from category set 2), NewCategoryID2 (2nd category from category set 2 if applicable)

I have a "categorysearch.aspx" page currently set up to deal with category set 1 and I'd like to use this same page to display the results of a category set 2 search - i.e. I'd like the SQL to search for

 WHERE CategoryID = @CategoryID OR NewCategoryID = @NewCategoryID OR NewCategoryID2 = @NewCategoryID2

where @CategoryID, @NewCategoryID, @NewCategoryID2 are appended as a querystring

I also need to keep the other parts of my SQL query after the "WHERE" - i.e. AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name

I think this might just be a case of putting brackets in the right places to make it work, however, I'm having a lot of trouble with it so any help you could offer me would be really appreciated.



2/17/2007 4:17:31 PM 16182 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 15


WHERE (CategoryID = @CategoryID OR NewCategoryID = @NewCategoryID OR NewCategoryID2 = @NewCategoryID2) AND (Size.MostPopular = 'True') AND (Size.Availability = 'True') ORDER BY plants.Latin_Name

2/18/2007 11:35:22 AM


Nice with the blossoming! If I were you, I'd stop doing it that way and instead writing it in code-behind - it's much simpler!

You can see how this is done, under the ******* below. Above it, I put some example code for getting the "snippets" in the right place. Note that there is a very fast full-text search method there too (using len instead of LIKE) - it seems like you have a lot of code, so it might come in handy.

Let me know if I'm not clear enough, but as I said, this is just example code.



ordet.Length > 1 Then '"ordet" simply means "the word" in Swedish, and in my code (not presented here) I loop it to parse several words, quotes etc

If ordet.StartsWith("-") Then

icke =

" NOT " 'becomes AND NOT (...)

ordet = ordet.Remove(0, 1)

'removes minus sign

End If

If ordet.Length > 1 Then

'the sql string is being built

If OnlynamesearchCheckBox.Checked = True Then 'search only the name

tillagg += "AND " & icke & " len(aname) > len(Replace(aname, '" & ordet & "', '')) " 'this is fast!


tillagg +=

"AND " & icke & " len(aname+ber+kontaktnamn) > len(Replace(aname+ber+kontaktnamn, '" & ordet & "', '')) " 'search all text fields

End If

End If


'The string itself, the "answer" to your question:

ResultDataSource.SelectCommand =

"SELECT * FROM Mytable " & _

"WHERE shown = 1  " &  tillagg & "ORDER BY sortering"



Coding is a nine-to-five job: Nine PM to Five AM.
2/18/2007 11:44:27 AM

Similar Artilces:

Precedence of "where" ("of", "is", "will")?
Nobody on #perl6 today could answer this one. Is: Str | Int where { $_ } the same as: (Str | Int) where { $_ } or: Str | (Int where { $_ }) ? Followup questions, Mr. President: What kind of operators are "where", "of", "is", and "will"? Is there a reason that S03 doesn't list them? What are their precedence(s)? -- Chip Salzenberg - a.k.a. - <> Open Source is not an excuse to write fun code then leave the actual work to others. Chip Salzenberg writes: &...

.ALLCOL("%COLUMN%", " ", ", ", ", ")
Do you know anyway for me to exclude a subset of columns returned by this function. We have two columns (rec_user and rec_datetime) which are in all of our tables, but when generating triggers I want automatically generate a script which does not include those two columns but does include all other columns in that table. Bruce I should add that I am using PD Bruce "Bruce Lamb" <> wrote in message > Do you know anyway for me to exclude a subset of columns returned by this > function. ...

regarding "in" and "=" in a SQL query
Hi, I am using a sybase database client . When i use "in" in a complex nested query say "select * from emp where emp_id in (select emp_id ..........)" then the execution time is really slow but in the same nested query when i use = as in "select * from emp where emp_id = (select emp_id ..........)", then the query is fast. Is there any particular reason for the same? Regards, Supreeth The obvious difference is that the "=" implies that the subquery can only return a single value as opposed to the "in" which implies that th...

How to give "And" and "OR" in my sql query?
      My layout (.aspx) have 3 TextBox and 2 Dropdownlist and 1 Button which just looks like Search KeyWord 【Texbox1】DorpDownList1 (have 2 items "And" and "Or")  【TextBox2】DorpDownList2 (have 2 items "And" and "Or") 【TextBox3】 Button ( this Button is for submit query)   1 "txtQuery" style="Z-INDEX: 101; LEFT: 152px; POSITION: absolute; TOP: 32px" runat="server" 2 Width="264px"> 3 "ddlBoolean" style="Z-INDEX: 102; LEFT: 152px; ...

quotes, quotes, quotes...
I am getting this error and I know what is causing it, but I have no idea how to fix it, any help would be great. The script steps through the /var/log/messages file on a linux server and puts The entries into a mysql database. However when it gets to the 'hlt' line in the messages file it just barfs. The single quotes are freaking it out. I know about quotes but not how to use in this situation. Thanks, Paul Error: May 27 17:53:00 localhost kernel: Checking 'hlt' instruction... OK. <----- doesn't like this in the messages file DBD::mysql::st exec...

How to adopt: Dim rowValues As Object() = {"","",""}?
Hi guys! I'm trying to adopt the code from the Editable Data Grid template. My underlying table has 3 columns: 1st: int(4), identity, autoincrement; 2nd:datetime(8); 3rd:varchar. What do I need to specify in Dim rowValues As Object() = {"","",""} instead of the first and second "" to get rid of the System.FormatException: Input string was not in a correct format… Thanks ...

"OUTPUT TO" Statement in "IF"
Hi All, I'm trying to execute the following statements: IF Condition THEN SELECT col FROM tbl; OUTPUT TO 'filename'; ELSE statements... END IF This gave me an error at 'SELECT' Can anyone tell me what is the correct approach? Thanks Stelios Stylianou > Hi All, > > I'm trying to execute the following statements: > IF Condition THEN > SELECT col FROM tbl; > OUTPUT TO 'filename'; > ELSE > statements... > END IF > > This gave me an error at 'SE...

"-" not "_"
I wrote a SQL statement in the data tab. I wrote a bunch of alaises as example ' word-type ' but when I hit the layout tab it converts the "-" to "_". So now my field name is ' word_type '. Is there any way to prevent this? CardGunner Don' use a hypen ( - ).  It isn't a valid character for column names.   See,289625,sid87_gci1188931,00.html   Here's an excerpt about column names: Letters as defined in the Unicode Standard 2.0 Decimal numbers from either B...

replace the "." with a ","
Oi.... I need to build a small programm in ASP.NET and chose to use C# for it.Now i got everything working but there's one little problem.the first textbox is a double. I need to make it so that when someone enters a "." then it gets replaced by a ","any ideas?Ghan  string blah = "";blah = blah.Replace(".", ",");Ryan Ryan OlshanASPInsider | Microsoft MVP, ASP.NEThttp://ryanolshan.comHow to ask a question...

" union " + " order by " sql statement?
hi, It has error message " column 'CAT' not found " , when i compiled in Interactive SQL ASA7. sorry, my english is poor. ======= what's wrong the sql statement as the below. SQL: select '' as CAT, 'ALL' as DES union select cat,des from category group by cat,des order by cat I want the result is : CAT DES ------- ------ ALL C Cat C B Cat B : : The component SELECT statements must each have the same number of items in the select list, and cannot con...

"Me" is better than "You"
Yes I know, strings are frozen. But let me talk about it, I really can't get through the idea of a PC talkin to me. I consider my PC as an extension of myself, not a dumb companion who addresses Me as You. Yes there are times when I get angry with Him while I work and get wrong calculations etc.., but it really is my fault, Me using wrong istructions and eventually wanting to find someone else to blame, but it's Me. And yes, I consider Thunderbird my mail program, reading my mail on my PC as Me. So I personally like to have Me in the header bar as a compact address ...

"To" and "From" missing
When I print emails, the words "To" and "From" are blank, even though the "To" name and "From name (addresser, addressee) do show up. This is not a problem for other users on my system. Suggestions In mailbox right click, view. On the message window, right click and choose print options. Make sure print header is checked. -- Barry Merchant NSC Volunteer SysOp *** no email unless requested please!! *** > In mailbox right click, view. On the message window, right click and > choose print options. Make sure prin...

Using "+" or "||"
Using SQLAnywhere 5.5.04, I've gotten into the habit of using "||" in ISQL to indicate a string concatenation. I needed to paste my SQL statement into the PowerBuilder script painter for some embedded SQL, and PB didn't like the "||" very much at all. I changed it to "+" and it seems to be ok. Do these two operators indicate ~exactly~ the same thing? moin, afaik these two's are not the same! if you're using "||" and any term is NULL then in the resultstring the term will be ignored if you use "+" then the resu...

"Using" or "With"
Hi all Please can someone enlighten to me as regards the difference with the "Using" and "With" statement when accessing data - which is better, what are the limitations and/or any pointers. Many thanks. Regards DaveDavid WinchesterPlease mark as answer if this is the solution.  using gives you the ability to use the connection and it closes the connection directlly after you finish using it. and there is no need to try- cach - finaly. there is no limitation on using USING keywordMuhanad YOUNISMCSD.NETMy Blog || My Photos || LinkedIn I have a dataobject the re...

Web resources about - Multiple "AND" and "OR" Statements in an SQL Query -

Resources last updated: 12/27/2015 10:32:51 AM