SQL Query - Use Like only if no exact match exists?

Question

I'm having an issue with a query that currently uses

LEFT JOIN weblog_data AS pwd ON (pwd.field_id_41 != '' AND pwd.field_id_41 LIKE CONCAT('%', ewd.field_id_32, '%'))

however I'm discovering that I need it to only use that if there is no exact match first. What's happening is that the query is double dipping due to the use of LIKE, so if it tests for an exact match first then it will avoid the double dipping issue. Can anyone provide me with any further guidance?

Answer 1

Hi, you're talking about short circuit evaluation.

Take a look at this article it might help you: http://beingmarkcohen.com/?p=62

Answer 2

using TSQL, run an exact match, check for num of rows == 0, if so, run the like, otherwise don't run the like or add the like results below the exact matches.

Answer 3

I can only think of doing it in code. Look for an exact match, if the result is empty, look for a LIKE. One other option is a WHERE within this query such that WHERE ({count from exact match}=0), in which case, it wont go through the comparison with LIKE if the exact match returns more than 0 results. But its terribly inefficient... not to mention the fact that using it meaningfully in code is rather difficult.

i'd go for a If(count from exact match = 0) then do like query, else just use the result from exact match.

Answer 4

It sounds like you want to join the tables aliased as pwd and ewd in your snippet based first on an exact match, and if that fails, then on the like comparison you have now.

Try this:

LEFT JOIN weblog_data AS pwd1 ON (pwd.field_id_41 != '' AND pwd.field_id_41 = ewd.field_id_32)
LEFT JOIN weblog_data AS pwd2 ON (pwd.field_id_41 != '' AND pwd.field_id_41 LIKE CONCAT('%', ewd.field_id_32, '%'))

Then, in your select clause, use something like this:

select
  isnull(pwd1.field, pwd2.field)

however, if you are dealing with a field that can be null in pwd, that will cause problems, this should work though:

select
  case pwd1.nonnullfield is null then pwd2.field else pwd1.field end

You'll also have to make sure to do a group by, as the join to pwd2 will still add rows to your result set, even if you end up ignoring the data in it.