CODEWITHSUNDEEP
mysqlsql
Nelson Sousa
Nelson Sousa

Reputation: 506

How to write a query to get route id based on multiple parameters?

I designed this simple model and I am with some difficulties to write the desirable query.

To start with, here's the markdown of the 3 tables that I have:

SELECT * FROM Routes;

Id Content
1 Route 1
2 Route 2

SELECT * FROM PointsOfInterest;

Id Name
1 Oceanário
2 Mosteiro dos Jerónimos
3 Torre de Belém

SELECT * FROM Routes_PointsOfInterest;

RouteId PointOfInterestId Order_Position
1 1 1
1 2 2
1 3 3
2 1 3
2 2 2
2 3 1

I need to find the RouteId for multiple points of interested in a known order. So, for example, I need to find the route corresponding to the points of interest 1, 2 and 3 in the order 1->2->3. So this one should return RouteId=1. However, for the same points of interest 1, 2 and 3, in the order (3->2->1) the RouteId should be 2.

Basically this means that the route from PoI 1 to PoI 3 is different from the route from PoI 3 to PoI 1.

I designed this tables, so maybe this isn't the best approach to design the tables. If it helps, I can alter all this schema.

Here's the minimum amount of code to reproduce my problem: http://sqlfiddle.com/#!9/0cdee8/1 or https://www.db-fiddle.com/f/v3GZPCHpmdyBRCFGCsr2im/0

Thank you for your help!

Upvotes: 0

Views: 647

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Hmmm . . . one method is aggregation and using strings for the match:

select rp.routeid
from Routes_PointsOfInterest  rp
where rp.PointOfInterestId in (1, 2, 3)
group by rp.routeid
having group_concat(rp.PointOfInterestId order by rp.position) = '1,2,3';

EDIT: For your question of only including 1,2,3, just remove the where clause:

select rp.routeid
from Routes_PointsOfInterest  rp
group by rp.routeid
having group_concat(rp.PointOfInterestId order by rp.position) = '1,2,3'

Upvotes: 1

Related Questions