SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql row comparison for each row

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Select * from table t1 cross join table t2

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The cross join return you the entire records specified in both tables.But How can Find remaining calculation.This is where I got stuck....

  4. #4
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have done something like this

    It doesn't meet my requirement but I am still tring to get the answer

    SELECT *,(select
    CASE WHEN (CASE WHEN a.uid<b.uid THEN 0 ELSE 1 END)
    THEN 10
    ELSE 9
    END as isp

    FROM `testing` a cross join testing b) as ordergp

    FROM testing c

    It gives me a subquery multiple row error .

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aniltc View Post
    The cross join return you the entire records specified in both tables.
    "the entire record" is what you get when you use the dreaded, evil SELECT *

    the cross join is what you want, but only one join (two copies of the table), not two joins like you tried

    if you have three users, a,b,c, then the cross join will give you --

    a:a
    a:b
    a:c
    b:a
    b:b
    b:c
    c:a
    c:b
    c:c

    if you add a WHERE condition, so that t1.uid <> t2.uid, then you will get the following --

    a:b
    a:c
    b:a
    b:c
    c:a
    c:b
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •