Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Note: I tried posting this on Stack Overflow but didn't get any good bites. I'm still researching this but wanted to see if any db czars can assist here.

I'm in the process of trying to troubleshoot some SQL 2012 code in an ASP/C# gridview search that I rolled myself. I have a search box that searches on specific fields but the twist here is that I only want results after a certain row.

In this database I have a sequence command that autogenerates a number in a column called 'record_number' for every entry in this table and only want results after record #500. The entries are created manually via a different page.

Right now my search code is as follows:

"SelectCommand="SELECT [record_number], [column_a], [column_b], [column_c], 
    [column_d], [column_e], [column_f], [column_g], [column_h], 
    [column_i], [column_j] 
  FROM [schema].[table_name] WHERE record_number >= 500 
  AND ([column_a] LIKE '%' + @column_a + '%') 
   OR ([column_c] LIKE '%' + @column_c + '%') 
   OR ([column_e] LIKE '%' + @column_e + '%')
   OR ([column_g] LIKE '%' + @column_g + '%')
   OR ([column_h] LIKE '%' + @column_h + '%')
   OR ([column_j] LIKE '%' + @column_j + '%')"

I can post my SelectParameters if needed or anything else. The challenge I'm facing is when I run a search for just the letter 'c' leveraging this code, the results that post have a record_number less AND greater than than 500. At this point I'm spinning my wheels as I'm not seeing where the problem lies.

If more information is needed by the masses, please let me know.

EDIT

Forgot this part - I've tried moving around the commands and consolidating the commands but no go.

share|improve this question
    
Wrap the ORs in an additional bracket pair i.e. ... record_number >= 500 AND (([column_a] LIKE...@column_j + '%')) –  Mark Storey-Smith Jun 12 '13 at 23:14
    
Also, no idea what you're trying to achieve with this but it's wrong on so many levels you should probably pop into chat to seek advice. –  Mark Storey-Smith Jun 12 '13 at 23:20
add comment

closed as too localized by Mark Storey-Smith, StanleyJohns, dezso, RolandoMySQLDBA, ypercube Jun 13 '13 at 13:12

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

up vote 2 down vote accepted

Your OR clause is breaking your filter.

SELECT [record_number], [column_a], [column_b], [column_c], [column_d], [column_e], [column_f], [column_g], [column_h], [column_i], [column_j] 
FROM [schema].[table_name] 
WHERE record_number >= 500 AND 
([column_a] LIKE '%' + @column_a + '%') 
OR ([column_c] LIKE '%' + @column_c + '%') 
OR ([column_e] LIKE '%' + @column_e + '%') 
OR ([column_g] LIKE '%' + @column_g + '%') 
OR ([column_h] LIKE '%' + @column_h + '%') 
OR ([column_j] LIKE '%' + @column_j + '%')

Basically, it's evaluating the AND at the same time as all the ORs, so if ANY OR matches, it'll hit. You'll want to bracket all the ORs together, like this:

SELECT [record_number], [column_a], [column_b], [column_c], [column_d], [column_e],     
[column_f], [column_g], [column_h], [column_i], [column_j] 
FROM [schema].[table_name] 
WHERE record_number >= 500 AND 
(
([column_a] LIKE '%' + @column_a + '%') 
OR ([column_c] LIKE '%' + @column_c + '%') 
OR ([column_e] LIKE '%' + @column_e + '%') 
OR ([column_g] LIKE '%' + @column_g + '%') 
OR ([column_h] LIKE '%' + @column_h + '%') 
OR ([column_j] LIKE '%' + @column_j + '%')
)

(I've broken the new ( ) out so you can see where it's needed).

share|improve this answer
    
That did it! Thanks! –  Techie Joe Jun 13 '13 at 15:38
    
Thanks for the explanation too. I really wanted to get under the hood and learn why its broken and what exactly it takes to make it work correctly. –  Techie Joe Jun 13 '13 at 16:19
add comment

@Mark is absolutely right. AND has higher precedence, so what your query is really saying is:

WHERE record_number >= 500 AND (column_a LIKE '%something%')

OR 

column_c LIKE '%something else%'

OR

column_e LIKE ...

What you want it to say is:

WHERE record_number >= 500

AND

column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ...

The way you do this is through parentheses. So:

WHERE record_number >= 500
AND
(column_a LIKE '%something%' OR column_c LIKE '%something else%' OR ... )

However I also agree with Mark's idea that this is wrong on many levels. Why have you hard-coded the query to not care about the first 499 rows? What happens if the user doesn't enter a parameter for one of the columns? Do you expect this to perform well when your table gets large and you are essentially forcing scans to find all of the matching rows?

share|improve this answer
    
Because I programmed the sequence in this table to start at 500. –  Techie Joe Jun 13 '13 at 15:39
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.