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 ;)</div