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
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
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.