Query Not Working Right!

Hello All,
I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. ThesearchString,Typeand theLocation (where the user who is searching belongs to).
When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.

For example:
Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c.
So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters.
Any help would be greatfull.

Query:

SELECT
Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate

FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID

WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @.SR + '%' OR [Client.SSN] LIKE '%' + @.SR + '%' OR [Client.LastName] LIKE '%' + @.SR + '%' OR [Client.FirstName] LIKE '%' + @.SR + '%' OR [Client.MiddleName] LIKE '%' + @.SR + '%' )

The last search criteria in your query will return rows when anyone of the[ClientDocuments.LeafName]/[Client.SSN]/[Client.LastName]/ [Client.FirstName][Client.MiddleName] contains@.SR, right? Make sure you need to do such a search, because there are so many fields to be searched so maybe some field meets the criteria while you're not aware of this. BTW, LIKE '%' + @.SR + '%'means any string contains@.SR, not only string which starts with@.SR|||

Start by either deleting all your brackets or use them correctly. Brackets are for quoting identifiers. The period in Client.MiddleName separates two identifiers (Table Name, and Field Name). [Client.MiddleName] would be a field named... Client.MiddleName. It's incorrect usage, and while it might fly sometimes, you should fix it. It's possibly causing the SQL Parser to go a little crazy.

The brackets as is are not necessary since neither Client nor MiddleName, etc have any characters in it that would otherwise be illegal without. You specify it as either Client.MiddleName or [Client].[MiddleName], but not [Client.MiddleName].

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

|||

Motley:

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

Haha, Hey I did not even see that, yeah it is a typo. Anyways, I will try breaking out the brackets. But do you think that the brackets would have cause the query to act funny?|||Looking it over, it appears to me that everything else is ok, but then again, sometimes I miss the obvious. Your logic is sound atleast.|||

Hey Thanks everyone for there help. I got the query to work perfectly.

Thanks again.