I need to compare all rows in a table and need to do some calculation on every comparison.
For eg: I have 3 users(a,b,c).I need to compare a->b and give some marks to b and insert this in another table and then a->c do the calculation and store in the above same table and then need to compare b->c and do the calculation and insert the calculated value to a table.then c->a,c->b,-a do same as above.
id,userid,lat,lng is the table structure. I need to return lat and lng in each comparison ( I mean When I do a user to user) and then assign a value to second user as described above.id is pk and userid will be a non repeat value and its unique .Basically while returning lat and lng I need to check the distance calculation for each users based on one to one comparison and giving marks based on the distance then storing these marks in another table based on the user id.
How can we do these kind of comparison ? should I Use cursor ? any other simple ways ?
I am able to write a simple join query like this but no idea how to do calculation and insert this into another table.
Select * from table t1 join table t2 on t1.userid!=t2.userid
Thanks but stll trying to get the answer based on the distance.I need to find the distance and apply points to each user.I have created like this but not in correct way
DELIMITER $$
DROP FUNCTION IF EXISTS GetDistance$$
CREATE FUNCTION GetDistance(
lat1 numeric (9,6),
lon1 numeric (9,6),
lat2 numeric (9,6),
lon2 numeric (9,6)
) RETURNS decimal (10,5)
BEGIN
DECLARE x decimal (20,10);
DECLARE pi decimal (21,20);
SET pi = 3.14159265358979323846;
SET x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180 ) + cos(
lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos( abs ( (lon2 * pi/180) -
(lon1 *pi/180) ) );
SET x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );
RETURN ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END $$
DELIMITER ;
I need to apply following points based on the distance
30 miles or less = 2 points
30 - 50 = 1 points
50 and up = 0 points
This is based on each user while comparing each row