MySQL match against with 2 fields

Question

i have a problem with match() against() in MySQL. I cant match 2 columfields against each other, only if i use a procedure, thats clear.

Now the problem: I have a query to search double entries (only technnical data):

SELECT t1.acid,t2.acid, t1.Brand, t2.ModelNameRough,t1.ModelNameDetail, t2.ModelNameDetail,.........
FROM ref_web_tb t1 join ref_web_tb t2
on t1.Brand = t2.Brand
and t1.ModelNameRough = t2.ModelNameRough 
and t1.BodyType ....(alot of other things to compare)
and (t1.acid < t2.acid)

but thats not enough. There is a field called "modelnamedetail" that locks like: "1.9 TDI COMFORT VARIANT DPF" or "1.9 TDI COMFORTLINE VARIANT DPF"

(only the line in comfort is different, but both of the cars have different prices and so on, but the same technical data)

So i have to match the t1.modelname against t2.modelname and use the score to check if the name is nearly the same. (otherwhise it would be a loooooot of data to check if there is a double entry, because there are alot of cars with the same technical data in the same roughmodel)

The procedure for the match() against() is no problem, i would do it like this: (just a test)

DELIMITER $$

DROP PROCEDURE IF EXISTS `offensichtlich_doppelte` $$
CREATE PROCEDURE `offensichtlich_doppelte` (search_string TEXT)
DETERMINISTIC
READS SQL DATA
BEGIN

 SELECT t1.acid, t1.Brand,t1.ModelNameRough,t1.ModelNameDetail, MATCH (t1.ModelNameDetail) AGAINST (search_string) AS score
     FROM ref_web_tb t1 

      limit 50;

END $$

DELIMITER ; 

but now i have no idea how to use the query results with the procedure?! Im totaly confused what to do now ;( Can someone give me a hint how i can solve the problem?

And another short question: Sometimes stuff like "gearboxtype" is null. If one of the technical fields is null, it wontget listed. Can i do something like:

SELECT *
FROM ref_web_tb t1 join ref_web_tb t2
on ....
and if gearboxtype is null then ignore gearboxtype
....

?

I thought something like:

SELECT *
    FROM ref_web_tb t1 join ref_web_tb t2
    on ....
    and if(t1.gearboxtype is null, ignore, t1.gearboxtype = t2.gearboxtype)
    ....

would work, but it seems like not, because i dont know how to ignore the case ;( (wait .. is case maybe the answer? )

Thanks for reading ;)

Answer 1

As for your first question, I think cursors is what you are looking for.

First, you need to declare some variables in your procedure, to store the data you get.

DECLARE a CHAR(10);
DECLARE b INT;

Since the cursor can be traversed just one time, you have to handle the NOT FOUND exception at the end of it.

DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

Then you declare a cursor to fetch your data.

 DECLARE results CURSOR
 FOR
 SELECT stuff, otherstuff FROM someTable;

This just creates the cursor. You have then open it, in order to actually execute the query:

OPEN results;

To get the results of your query, then, you iterate over the cursor:

read_loop: LOOP
    FETCH results INTO a, b;
    IF done THEN
        LEAVE read_loop;
    END IF;

    --do your business here with a and b

END LOOP;

And, finally, close the cursor:

CLOSE results;

Hope this helps.