Basic SQL: Multiple AND/OR nightmare in SELECT statement

This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...

I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.

SELECT *
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
ParentID1 = COALESCE(@.taxparent, ParentID3) OR
ParentID2 = COALESCE(@.taxparent, ParentID2) OR
ParentID3 = COALESCE(@.taxparent, ParentID3) OR
ParentID4 = COALESCE(@.taxparent, ParentID4) OR
ParentID5 = COALESCE(@.taxparent, ParentID5) OR
ParentID6 = COALESCE(@.taxparent, ParentID6) OR
ParentID7 = COALESCE(@.taxparent, ParentID7) OR
ParentID8 = COALESCE(@.taxparent, ParentID8)

The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.

Has anyone got a good tip to help me resolve this puzzle?

Hi

You can also use IN and NOT IN as well as Having caluse to verify condition. As ( and ) paranthesis can also help you to verify condition on a condition,

|||Hi Akbar,

Sorry, but that is not really helpful.

As far as I understood, IN is used to test multiple values against a single column. I am testing a single value against multiple columns.

HAVING is used with aggregate values. I am not using those.

As I already wrote, using parenthesis does not work for me, or I do not know how to properly apply them in this particular case.

I am still with my hands in my hair on finding a solution to this, so I would appreciate any help.|||All right, fair enough, after some study, I was able to simplify the code using 'IN', that -new to me- could also be used for testing a single value against multiple columns. But I am stuck with the same problem that it won't combine with the rest of the conditions in order to yield the desired results!

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' OR
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me too many results and

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me no results....

|||OK, another discovery! When I comment out:

ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%'

It does actually succesfully combine the different criteria!

This must mean something goes wrong with those lines only...

Here is the entire code of the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [petrander].[DynamicQuery]
@.taxparent int = NULL,
@.museum int = NULL,
@.collection int = NULL,
@.binomen Nvarchar(254) = NULL,
@.locality Nvarchar(254) = NULL
AS
SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE 'N%' + @.binomen + '%' AND
Locality LIKE 'N%' + @.locality + '%' AND
@.taxparent IN (ParentID1,
ParentID2,
ParentID3,
ParentID4,
ParentID5,
ParentID6,
ParentID7,
ParentID8)

Could the problem lie in combining null values with the LIKE 'N%' + statements?|||Problem lay somewhere else and solution can be seen in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620363&SiteID=1