need assistance adding textual search to my generic search query

I have a basic search query on my site that allows users to find folks that are near them. The search also contains pieces of info about the user (this is for a bartering website) such as what they have to offer. It works great, but now I want to add onto it that allows for a search string. So if I want to search for the term "books", I should get all users that are offering books. I have two tables that handles offer data, tblOffers, which contains generic data for each offer (offerID is the ID field, offer is the name of the offer) and tblUserOffers, which links an offer to an individual user (offerID matches the tblOffers.offerID and userID links the offer to a user). I've built the following sql with help from a few experts here on EE, and I essentially want to add in logic along the lines of

where tblOffers.Offer LIKE '%chair%'

However, I'm unsure of the best place to put it to maintain efficiency. Here is the full query, any help is greatly appreciated :)

select distinct u.userID, fullName = u.firstName + ' ' + u.lastName, u.displayName, u.city, u.stateProvince, Round( dbo.CalculateDistance(-71.43520, 42.32320, z.Longitude, z.Latitude), 2 ) As Distance, 
	profilePicture = case when isNull( u.profilePicture, '' ) = '' then '/images/person.png' else u.profilePicture end, 
	confirmed = case u.confirmed when 1 then '<some text here>' else '' end, 
	u.lastLogin, oa1.offers, uc.offerCount, totalUsers = COUNT( * ) OVER() 
From tblUsers u 
	OUTER APPLY ( 
		SELECT STUFF( (
			SELECT ', ' + offer  	
			FROM tblOffers o 		
				inner join tblUserOffers uo on uo.offerID = o.offerID 	
			WHERE u.userID = uo.userID 
			FOR XML PATH ('') 
		)  , 1, 1, '') 
	) AS OA1 (offers) 
inner join ( SELECT uo.userID , count(*) offerCount FROM tblUserOffers uo GROUP BY uo.userID )  uc ON u.userID = uc.userID 
inner join ZipCodes z on z.zipCode = u.zipCode and 
	u.userID <> 100 and 
	Longitude Between -72.41289 And -70.45751 And 
	Latitude Between 41.59901 And 43.04739 And 
	dbo.CalculateDistance(-71.43520, 42.32320, Longitude, Latitude) <= 50 and 
	status = 1  
order by Distance, u.lastLogin 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 

Open in new window

LVL 34
Big MontySenior Web Developer / CEO of ExchangeTree.org Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
Search is a complex subject if you just want to use sql, would be hard to do fuzzy search, but the 'like' approach should work for a simple search if you want to consider user typos you may want to consider functions like
soundex as well:

https://technet.microsoft.com/en-us/library/ms189282(v=sql.105).aspx
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
For now I just want to stick to using LIKE
0
Nitin SontakkeDeveloperCommented:
You can add below between line 11 and 12 of your posted code above.

     and o.Offer LIKE '%' + isnull(@searchTerm, '')  + '%'

Open in new window


Here the @searchTerm is a parameter which needs to be passed. I have no idea how exactly you are calling this query. Is this part of a stored procedure? Built dynamically in middle layer and passed on to the database?

As indicated already, searches can become very complicated and eventually you may wish to look into anything between Full Text Search to ElasticSearch!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
@Nitin:

Thanks for the suggestion. I forgot to mention my initial attempt was exactly what you suggested, and I didn't get the results back I was expecting. My apologies. To answer your other questions, this is part of a stored procedure that I call from my web app. I am eventually going to move to full text search, but for now I just want to have something that's "quick & dirty", that's why I am going this route.
0
Nitin SontakkeDeveloperCommented:
That's okay. What precise changes did you make and what unit tests did you execute to conclude - didn't get the results back I was expecting...details are very important as the change is really very minor.

Did you insert the exactly same piece of code as it is given by me? You added the parameter? Executed the code in SQL Server Management Studio? Bypass the web as it is not quire required for the testing purpose.

Hope you are with me so far.
0
Nitin SontakkeDeveloperCommented:
On second thought, copying and pasting sql statement above in SSMS is also fine for testing purpose until proven okay to put in sp.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
my apologies, I should know better as I am usually the one answering questions...need more caffeine :)

I ran the following sql directly in SSMS:

select distinct u.userID, fullName = u.firstName + ' ' + u.lastName, u.displayName, u.city, u.stateProvince, Round( dbo.CalculateDistance(-71.43520, 42.32320, z.Longitude, z.Latitude), 2 ) As Distance, 
	profilePicture = case when isNull( u.profilePicture, '' ) = '' then '/images/person.png' else u.profilePicture end, 
	confirmed = case u.confirmed when 1 then '<some text here>' else '' end, 
	u.lastLogin, oa1.offers, uc.offerCount, totalUsers = COUNT( * ) OVER() 
From tblUsers u 
	OUTER APPLY ( 
		SELECT STUFF( (
			SELECT ', ' + offer  	
			FROM tblOffers o 		
				inner join tblUserOffers uo on uo.offerID = o.offerID 	
			WHERE u.userID = uo.userID and o.Offer LIKE '%chairs%'
			FOR XML PATH ('') 
		)  , 1, 1, '') 
	) AS OA1 (offers) 
inner join ( SELECT uo.userID , count(*) offerCount FROM tblUserOffers uo GROUP BY uo.userID )  uc ON u.userID = uc.userID 
inner join ZipCodes z on z.zipCode = u.zipCode and 
	u.userID <> 100 and 
	Longitude Between -72.41289 And -70.45751 And 
	Latitude Between 41.59901 And 43.04739 And 
	dbo.CalculateDistance(-71.43520, 42.32320, Longitude, Latitude) <= 50 and 
	status = 1  
order by Distance, u.lastLogin 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 

Open in new window


it doesn't matter what I put in for the search term, I always get the same results back. I verified that I only have ONE user with "chairs" listed as an offer.
0
Nitin SontakkeDeveloperCommented:
my apologies, I should know better as I am usually the one answering questions...need more caffeine :)

Incidently just stubbled upon one thread where you have answered the question about ADODB, so I get that completely. Not an issue.

The query you / we are modifying is only impacting the contents of the offers column (line 4, second one). Are the contents of that are getting changed?

If yes, we can then modify and bring expected filter out. That will limit the rows. A join same as the one above will be required again.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
ya i typically hang out in the classic asp zone, that's my specialty :)

The query you / we are modifying is only impacting the contents of the offers column (line 4, second one). Are the contents of that are getting changed?

I'm not sure what you're asking here. If you're asking if the returned data is different from when I don't add in the LIKE code, then the answer is no, it's the same data being returned. If I've misunderstood, I'll need some clarification.

For some context, you can have a look at https://www.experts-exchange.com/questions/29017932/SQL-Query-that-uses-count-over-possible-to-use-distinct.html. Scroll down to where PortletPaul gets involved, and you'll see what the query is all about.
0
Nitin SontakkeDeveloperCommented:
Between line 21 and 22:

left outer join [dbo].[tblUserOffers] tuo on u.[userID] = tuo.[userID]
left outer join [dbo].[tblOffers] tor on tuo.[OfferID] = tor.[OfferID]
where 1 = 1
  and tor.[Offer] like '%chairs%'

Open in new window


I am afraid of unexpected results, but try it nonetheless. All we are doing SELECTing the data anyway.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitin SontakkeDeveloperCommented:
Had a look at that thread. Thank you! That helps!

You know there is one column called "offers" which is actually the combination of all offers text separated by comma. That's the one i am referring to. When you add the where clause (where we have been attempting so far) the number of rows WILL NOT affect, but the contents of the offers column MUST change.

You may wish try in two diff query windows and try.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
so this suggestion works, sort of. It's working for some search terms, but not others, where I expect it to. for example, if I run:

select * from tblUserOffers where offerid in ( select offerID from tbloffers where offer like '%wood%' )

Open in new window


I get 5 results returned. However, if I put '%wood%' in the original query, i get nothing returned. I'll have to inspect the data more, i'm guessing that those entries are probably outside of the 50 mile radius, I just want to verify that. Unfortunately I have to jump on something else for work (this is my side project), so I won't be back on it until later tonight.

I appreciate your help. I'll be back on around 7pm EST or so (while I watch my college alma matta hockey team compete in a city tourney called the Bean Pot). I don't expect you to be around, but I think I'm on the right track here and will report back then.

I very much appreciate the help :)

Cheers
Josh
0
Nitin SontakkeDeveloperCommented:
Okay. Best of luck!

I am in India and was going to bed after this comment anyway, so thanks!
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
many thanks for the assistance!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.