how make mysql query for that case

Question

I make my real case more simple.

The case:

There are multiple curves they are passed in points and each of them have final 1 point.The final point represented in database as biggest point_order value of the curve.

Should be find curves that pass in specific point and have same final point (same point_id)

The case(tables):

point table:

point_id|x|y

Edit:

curve_points table example - find all curves that have same point_id=80 and same final 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

Output result should be:

  |curve_id|
  |119     | 
  |76      |

Because the curves 119,76 have same final point=6 and have same point 80. Curve 90 not because the point 6 not his final point

psedocode function - need to add code for choose same final point:

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 (the existed query there not works)

Thanks

Do you mean that final point and pass point are the same or you need curves which their final points are the same? — Maryam Arshi, Feb 19 '13 at 10:54
Thanks for comments I edit the question - its contain example of input and output. — Ben, Feb 19 '13 at 11:46

valex · Answer 1 · 2013-02-19T12:59:24.220

1

If I've got it right. It is something like this:

SQLFiddle demo

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

edited Feb 19 '13 at 12:59

answered Feb 19 '13 at 11:17

valex

23,966
7
43
60

Query now is fixed according with your data. – valex Feb 19 '13 at 12:59
Why not? It selects all pairs according your conditions. In your example there are two groups of curves that contain point - 80 and have the same final point . 76,119 have final point 6 and the second group 70,90,92 have final point 99 – valex Feb 20 '13 at 05:21

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

First thing I want to ask, how Curve table is creating relation with point table? There must be REDUNDANT Curve_ids to map them with Point table.

If it is possible to change your Database Structure, you can use MySQL Geometry, which has built in classes like Point and Curve. You can check if two curves crosses and many more using built in functionality.

I found this one related.

edited May 23 '17 at 11:49

Community

1
1

answered Feb 19 '13 at 11:29

Mayukh Roy

1,815
3
19
31

1

thanks, i fix the mistake - curve_points table and not curve only table – Ben Feb 19 '13 at 11:55
Its existed database can't change schema – Ben Feb 19 '13 at 16:33

how make mysql query for that case

2 Answers2