0

I'm trying to build a query which will get me a field which is mutual for two rows. This is the data I have presented in Excel (it's the same in the SQL table):

enter image description here

We have two dropdowns, one for start and end point of the trip. Once the user will select the start point, the dropdown is filtered to get only the destinations which are possible from that starting point.

Now, based on the screenshot, if the user selects 'Mazarron' for starting point and 'Albacete' for end point I want to get the name of the lines which offer that option, based on the start and end point, and that would mean also that the Stop Order of the end point will need to be larger from the Stop Order value of the start point. In our case I should get 'Aguilas-Madrid' and 'Puerto de Mazarron-Madrid' but not 'Puerto de Mazarron-Madrid' line.

What can be the best approach to filter those results when I'll use two parameters or @start and @end point of the trip?

halfer
  • 19,824
  • 17
  • 99
  • 186
Laziale
  • 7,965
  • 46
  • 146
  • 262
  • can you add a sample of a query you've tried? – rhealitycheck Oct 13 '14 at 20:16
  • @rhealitycheck I tried "Where NameOfTown = 'Mazarron' OR NameOfTown = 'Albacete'" but I'm getting records which are not mutual as well, like the third example in the screenshot – Laziale Oct 13 '14 at 20:18
  • 1
    Please edit your question and add sample data and desired results. Also, decide whether you are using MySQL or SQL Server. – Gordon Linoff Oct 13 '14 at 20:21
  • @GordonLinoff I'm using SQL Server. Desired results are explained below the screenshot and also sample data, that's the data which exists in the sql table. Thanks for your help – Laziale Oct 13 '14 at 20:22
  • oh yeah OR isn't going to work because it'll pick any lines that have either option. i'll throw a simple sample together – rhealitycheck Oct 13 '14 at 20:22
  • Use self join, see: [Explanation of self-joins](http://stackoverflow.com/q/2458519/3908097) – Rimas Oct 13 '14 at 20:34

1 Answers1

0

this is a relatively simple example and there are other ways you can do this. but this should work to get you what you want.

without stop order

select line 
from mytable 
where nameoftown = @end 
and line in (
select line from mytable where nameoftown = @start)

as requested an update with the stop order, this is a little more complex and i haven't tested but should be about what you need:

select line 
from mytable end
join (select line, stoporder from mytable where nameoftown = @start) start 
     on start.line = end.line and start.stoporder < end.stoporder
where end.nameoftown = @end
rhealitycheck
  • 650
  • 3
  • 8
  • 19