How to make a mysql query for this case
I am making my real case simpler.
Happening:
There are several curves that they represent in points, and each of them has an endpoint of 1 point. The end point is represented in the database as the highest point_order of the curve.
Find curves that pass at a certain point and have the same end point (same point_id)
Case (tables):
Point table:
point_id|x|y
Edit:
Example tablecurve_points - find all curves that have the same point_id = 80 and the same end point:
id|curve_id|point_id|point_order
|119 |6 |12
|119 |80 |9
|119 |1000 |1
|76 |80 |7
|76 |6 |9
|76 |2 |2
|90 |80 |7
|90 |6 |9
|90 |99 |15
The output should be:
|curve_id|
|119 |
|76 |
Since curves 119.76 have the same end point = 6 and have the same point 80. Curve 90 is not because point 6 is not its end point
Psedocode function - code needs to be added to select the same endpoint :
function findCurvesForSamePointAndSameFinalPoint(pointID){
query="SELECT curve_id FROM curve INNER JOIN point GROUP BY curve_id HAVING point_id="+pointID+";";
return getDATABASEResult(query);
}
Edit2: Online sql with some data to test: http://sqlfiddle.com/#!2/59e9f/1 (existing query doesn't work there)
thank
source to share
If I succeed. It goes something like this:
select distinct c1.curve_id,(select point_id from curve t1
where t1.curve_id=c1.curve_id
order by point_order desc
limit 1)
TheLastPoint
from curve c1
join curve c2 on
(select point_id from curve t1
where t1.curve_id=c1.curve_id
order by point_order desc
limit 1)
=
(select point_id from curve t2
where t2.curve_id=c2.curve_id
order by point_order desc
limit 1)
And c1.curve_id<>c2.curve_id
where c1.curve_id in (select curve_id from curve where point_id=80)
and
c2.curve_id in (select curve_id from curve where point_id=80)
order by TheLastPoint,c1.curve_id
source to share
First of all, I want to ask, how does the curve table create a relationship to the point table? There must be REDUNDANT Curve_ids in order to map them to the point table.
If you can change the structure of your database, you can use MySQL Geometry , which has built-in classes such as Point and Curve . You can check if two curves intersect and many more using the built-in functionality.
source to share