This is my query:
SELECT Oggetti.IDOggetto, Oggetti.IDPersona, Oggetti.NomeOggetto
FROM Oggetti INNER JOIN Person ON Oggetti.IDPersona = Person.ID
WHERE (@Name IS NULL OR Person.Name=@Name) AND
(@City IS NULL OR Person.City=@City) AND
(@Num IS NULL OR Person.Num=@Num)
When I entered it in the query builder form, it was "rewritten" in this way:
SELECT Oggetti.IDOggetto, Oggetti.IDPersona, Oggetti.NomeOggetto
FROM Oggetti INNER JOIN Person ON Oggetti.IDPersona = Person.ID
WHERE (@Name IS NULL) AND (@City IS NULL) AND (@Num IS NULL) OR
(@Name IS NULL) AND (@Num IS NULL) AND (Person.City = @City) OR
(@City IS NULL) AND (@Num IS NULL) AND (Person.Name = @Name) OR
(@Num IS NULL) AND (Person.City = @City) AND (Person.Name = @Name) OR
(@Name IS NULL) AND (@City IS NULL) AND (Person.Num = @Num) OR
(@Name IS NULL) AND (Person.City = @City) AND (Person.Num = @Num) OR
(@City IS NULL) AND (Person.Name = @Name) AND (Person.Num = @Num) OR
(Person.City = @City) AND (Person.Name = @Name) AND (Person.Num = @Num)
The question is: WHY???
In the particular case I have found a solution but I don't know if it applies also to other cases.
The solution is to write the WHERE condition a bit different. The new query is:
SELECT Oggetti.IDOggetto, Oggetti.IDPersona, Oggetti.NomeOggetto
FROM Oggetti INNER JOIN Person ON Oggetti.IDPersona = Person.ID
WHERE (@Name IS NULL OR @Name=Person.Name) AND
(@City IS NULL OR @City=Person.City) AND
(@Num IS NULL OR @Num=Person.Num)
The diffence is "@Name=Person.Name" instead of "Person.Name=@Name" (and the same with City and Num).
(@Name IS NULL OR Person.Name=@Name) AND
(@City IS NULL OR Person.City=@City) AND
(@Num IS NULL OR Person.Num=@Num)
(@Name IS NULL OR @Name = Person.Name) AND
(@City IS NULL OR @City = Person.City) AND
(@Num IS NULL OR @Num = Person.Num)
Why this behaviour ??? Any suggestions?
