SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    nowhere
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Compare data within a column

    Ok first of i would like to do this with out a temporary table but if thats the only way then i'll survive. I have single column table of integers. I want to compare one part of the column with a different part and return every match that it has in common.

    So lets say i have the column
    1
    3
    6
    9
    12
    2
    5
    6
    8
    9
    13
    6
    9
    10
    ect.

    Now for simplicitys sake lets say i want to compare the ranges 0-4 and 5-10
    which would be the number sets 1,3,6,9,12 and 2,5,6,8,9,13
    my desired result is 6,9 . Also im not quite sure how to use the row numbers of the integers to retrive sets of data. Is LIMIT the only way. These strings of data can be up to 10,000 integers so just reading it into php and parsing would be too costly.

    This is what i have so far, I am using a self join but the way i am accomplishing this is innefficient in both space and operation time.
    I generate sql code with php
    'SELECT * FROM post_table t1,post_table t2
    WHERE t1.post = t2.post AND
    t1.index <='.$max1.' AND
    t1.index >= '.$min1.' AND
    t2.index <='.$max2.' AND
    t2.index >='.$min2;
    the variables for the sample table i gave would be as follows:
    $min1=0 $max1=4 min2=5 max2=10
    The min and the max value are in a precaculated table.

    My main problems with this are that i have to go by an index to retrive the values and that i dont know how to check three columns against eachother efficently.
    t1.post=t2.post=t3.post doesnt work.

    I dont know exactly how to figure the code but if sql could read my mind i would write it like this.

    SELECT * FROM table t1,table t2, table t3
    (t1 LIMIT 5,10) JOIN ON
    (t2 LIMIT 25,75) WHERE t1.num=t2.num JOIN ON
    (t3 LIMIT 193,244) WHERE t2.num=t3.num;

    I know that this doesnt work/make much sense, but its the best i can come up with.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    single column table? then there's no way to do what you want

    tables have no built-in row sequence

    the only way to achieve row sequence is by ORDER BY, and if it's a single column table, you have only one choice for the ORDER BY, and then there's no way to get the 1,3,6,9,12 grouping you want

    rudy


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
  •