CODEWITHSUNDEEP
c#sql
Kohl
Kohl

Reputation: 44

SQL help needed combining WHERE, AND, OR

I'm trying to combine WHERE, AND, OR. But the results I'm getting aren't what I was hoping for :S I'm trying to select the name from a table where combobox1 could be in either of the columns 2-4 and the type and region has to be Italy and Bus.

string SqlQry = "SELECT [Name] FROM [Table] WHERE [Column 2] = @1 OR [Column 3] = @2 OR [Column 4] = @3 AND [Region] = @4 AND [Type] = @5 ORDER BY [Name] ASC";

                cmd.Parameters.AddWithValue("@1", Combobox1.SelectedItem);
                cmd.Parameters.AddWithValue("@2", Combobox1.SelectedItem);
                cmd.Parameters.AddWithValue("@3", Combobox1.SelectedItem);
                cmd.Parameters.AddWithValue("@4", "Italy");
                cmd.Parameters.AddWithValue("@5", "Bus");

Any help will be much appreciated.

Upvotes: 1

Views: 65

Answers (1)

juergen d
juergen d

Reputation: 204756

Add parentheses

WHERE 
(
  [Column 2] = @1 OR [Column 3] = @2 OR [Column 4] = @3
) 
AND [Region] = @4 
AND [Type] = @5

because due to operator precedence and binds stronger than or.

You could improve this to

WHERE @1 in([Column 2], [Column 3], [Column 4]) 
AND [Region] = @2 
AND [Type] = @3

cmd.Parameters.AddWithValue("@1", Combobox1.SelectedItem);
cmd.Parameters.AddWithValue("@2", "Italy");
cmd.Parameters.AddWithValue("@3", "Bus");

Upvotes: 5

Related Questions