troubleshooting Question

SQL Server - Help! Tricky Query

Avatar of dteshome
dteshome asked on
Microsoft SQL Server 2008Windows Server 2008
4 Comments1 Solution281 ViewsLast Modified:
I have the following query.  I replaced the UDF call because the use of "...LIKE..." is much faster - but that is not the issue;

The problem is with the AND clause where there is '**'
The column m.Keyword holds comma separated keywords.  I need to return, say, the keyword 'pool'
but the query returns records with the following format"
pool (where the keyword is at the start of the string)  -- okay
, pool  -- okay
View, pool, fixer  -- okay
, Room for pool  -- not okay

How do I ensure that I get what I expect?

Thank you.


select TOP 601 a.property_type,a.mlsnum,a.status,dbo.fn_sort_status(a.status), m.keyword -- 559 in 0 secs
from mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where (((a.lp BETWEEN 0 AND 999000)) AND ((a.status IN (5))))
AND (a.br >= 3) AND (a.ba >= 2) AND (a.sf >= 1200)
AND (a.property_type = 0)
**AND (m.keyword like  '%Bank Owned%' OR m.keyword like '%Builder/Developer%' OR m.keyword like '%Fixer%' OR m.keyword like '%pool%' OR m.keyword like '%Beverly Hills Adjacent%' OR m.keyword like '%Turnkey%')
--AND (dbo.fn_mvo_searchall_rtrn_bln_DT('Bank Owned,Builder/Developer,Fixer,pool,Beverly Hills Adjacent,Turnkey',m.keyword)  = 1)
--
KEYWORD
----------------------------
Turnkey
Move-in,Pool,Turnkey,Unfurnished,Yard
Pool
Move-in,Pool,Turnkey,Yard
Room for Pool,Room for Tennis Court,Tennis Court,Yard
Granite,Move-in,Pool,Remodeled,Turnkey,Yard
Court Approval,Foreclosure,Room for Pool,Unfurnished,Vacant,Yard
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros