Conditional Processing inside mySQL statement

Databases
1

Hi all

I need to try and optimise an application that has to return a “best guess” at finding a client name in a a client database containing ~500k records.

The client data is drawn from many sources and there is a lot of duplications… nothing we can do about this now.

The problem is that “John Smith” might exist as John Smith, J Smith, John (no surname), (no firstname) Smith

Is this possible in mySQL?

In english I want to do this

SELECT firstname, surname, ‘High’ as confidence FROM records WHERE firstname = ‘John’ and Surname = ‘Smith’;

IF records returned, END
IF NO records returned THEN
BEGIN

SELECT firstname, surname, ‘Medium’ as confidence FROM records WHERE left(firstname,1) = ‘J’ and Surname = ‘Smith’ and firstname <> ‘John’;

END

IF records returned, END
IF NO records returned THEN
BEGIN
… and so on.

I will have 3 or 4 attempts at finding records and then the code will give up and return no records at all.

Is this possible in mySQL?

2

Yup… pretty much how you typed it.

:slight_smile:

3

Can you point me to a source for the correct syntax for defining and naming recordsets? I must be looking in the wrong place in the mySQL documentation. Do I have to define the recordsets as cursors and if so do I have to iterate through them to determine if there are no records - or can I define them, execute them and then inspect data about them?

4

It’s been a while since I’ve used MySQL, but I think it would be something like:


IF EXISTS (SELECT firstname, surname FROM records WHERE firstname = 'John' and Surname = 'Smith';)
BEGIN
    SELECT firstname, surname, 'High' as confidence FROM records WHERE firstname = 'John' and Surname = 'Smith';
END 
ELSE IF EXISTS(SELECT firstname, surname FROM records WHERE firstname LIKE  'J%' and Surname = 'Smith';)
BEGIN
    SELECT firstname, surname, 'Medium' as confidence FROM records WHERE firstname LIKE  'J%' and Surname = 'Smith';
END
ELSE IF EXISTS(SELECT firstname, surname FROM records WHERE firstname = 'John';)
BEGIN
...
END
END
5

Thanks but it appears that IF and CASE do not seem to be able to be used outside of stored procedures… which is quite irritating. Also, EXISTS() seems to be only allowed inside a SQL query.

So this works fine: SELECT IF(EXISTS(select ‘a’),1,0)

But this does not:

IF EXISTS(select 1)
THEN
SELECT 1
ELSE
SELECT 0
END IF;

At least, this does not work in 5.1.41-3 ubuntu12.9

I miss MSSQL :frowning:

6

yes, with a UNION you can do it all in one query

SELECT 1         AS confidence_number
     , 'High'    AS confidence 
     , firstname
     , surname
  FROM records 
 WHERE firstname = 'John' 
   AND Surname = 'Smith'
UNION ALL
SELECT 2         AS confidence_number
     , 'Medium'  AS confidence 
     , firstname
     , surname
  FROM records 
 WHERE firstname LIKE 'J%' 
   AND firstname <> 'John'
   AND Surname = 'Smith' 
UNION ALL
 ... and so on
ORDER
    BY confidence_number

this might return more rows than you possibly may want, but due to overhead it’s probably more efficient than running three or more separate queries

7

Thanks for this but unfortunately it would not work in this specific case. It was using a union query but this was returning far to many rows. I am currently handling the query logic in the application layer (so the application does query A, and if that does not return anything, then query B etc) but I was hoping to be able to cut down on database calls and have it all done in the SQL server.

8

sounds like you want a stored procedure

9

A little change to the union will make it word

set @found=0;
SELECT 1 AS confidence_number,@found:=1 as found
, ‘High’ AS confidence
, firstname
, surname
FROM records
WHERE firstname = ‘John’
AND Surname = ‘Smith’
UNION ALL
SELECT 2 AS confidence_number, @found:=1 as found
, ‘Medium’ AS confidence
, firstname
, surname
FROM records
WHERE @found=0 and firstname LIKE ‘J%’
AND firstname <> ‘John’
AND Surname = ‘Smith’
UNION ALL
… and so on