Select query logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query logic

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-05-01 : 05:55:23

Hi,

This is a really stupid one and i should know the answer but i'm having one of those days...

I would expect this query below only to return results if a record has a Y in the General_Public_Facing field AND one of the other of the values were true that it is searching. For some reason query is ignoring everything else and as long as there is a Y in General_Public_Facing it will display the result which is not what I want. Something must be contained / matched in one of the other search fields or I do not want to display any results, even if there is a Y in General_Public_Facing field.

What have i got wrong here? Thanks for looking.



SELECT General_Public_Facing, company, type_of_business, Business_Overview, services FROM wce_contact where ((General_Public_Facing = 'y') and (company like '%%' OR type_of_business like '%%' OR Business_Overview like '%%' OR services like '%%'))

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 07:18:55

When you have a condition like "company LIKE '%%'" that will evaluate to true for any company (as long as it is not null). That would explain why you are getting all the records. Is something like the following what you want to do?

WHERE
	(
	    (General_Public_Facing = 'y')
	    AND (
	            company LIKE '%ABC Widgets%'
	            OR type_of_business LIKE '%Retailing%'
	            OR Business_Overview LIKE '%Domestic%'
	            OR services LIKE '%Plumbing%'
	        )
	)

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-05-01 : 08:28:52

Hi,

Thanks for your reply.

Basically a user is going to have one input field where they can type a word or a stings of words and click a search button.

If the General_Public_Facing is set to Y then whatever is type in that field is going to be dynamically set into the %% for the company, type_of_business, Business_Overview, and services fields so the user might type car cleaning and the database of x thousand records should return the one result where the service field contains that value. I only have one result in my database that matches those criteria.


SELECT General_Public_Facing, company, type_of_business, Business_Overview, services FROM wce_contact where
	(
	    (General_Public_Facing = 'y')
	    AND (
	            company LIKE '%car cleaning%'
	            OR type_of_business LIKE '%car cleaning%'
	            OR Business_Overview LIKE '%car cleaning%'
	            OR services LIKE '%car cleaning%'
	        )
	)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 08:42:23

So then you need something like this perhaps?

DECLARE @search VARCHAR(255);
SET @search = 'car cleaning';

SELECT 
	General_Public_Facing, 
	company, 
	type_of_business, 
	Business_Overview, 
	services 
FROM 
	wce_contact 
WHERE
	(
	    (General_Public_Facing = 'y')
	    AND (
	            company LIKE '%'+@search +'%'
	            OR type_of_business LIKE '%'+@search +'%'
	            OR Business_Overview LIKE '%'+@search +'%'
	            OR services LIKE '%'+@search +'%'
	        )
	)

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-05-01 : 09:48:52

That did the trick ;-) Many thanks for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 10:43:58

Very welcome.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources