mysql - select data based on multiple criteria (cloest value) -

I am using MySQL . I'm trying to create something And just can not find a solution to any problem.

The lookup to the my code> table is shown in the example below.
Select criteria:
. > my.id and my.route1 = L.route1 and My.route2 = l.route2 and my.utc = l.utc
Where my.stime is closest or similar to L.stime < Br>
) my.id = 2 calls to l.id = 1, l.time = 7777 since my.id & lt ; & Gt; L.I.D. and the rest are the same. In the call of my, id = 5 , the option is l.id = 3, l.time = 9999 and < Code> l.id = 4 options, since L.time = 7979 since my.id . However, my.stime = 2220 since l.stime = 2222 to l.stime = 3333 , l.id = 3, l.time = 9999 will be selected.
ex) Selecting an example example of my, id = 6 if "closest" is the same.
ex) Mea, id = 7 is for returning zero if the criteria are not met for example.


  Table: Lookup (L.) ID route 1 route 2 UTC stem ATM --- | -------- | -------- | ----- | ------- | ------ 1 11 22 111 1111 7777 2 11 22 111 1111 8888 3 22 33 222 2222 99 99 4 22 33 222 3333 7979 5 22 33 222 3335 8989 Table: My (Mary.) | Results ID route 1 route 2 UTC boxer | L.id l.etime --- | -------- | -------- | ----- | ------- | ------- | ---------- | 2 11 22 111 1111 | 1 7777 5 22 33 222 2220 | 3 99 99 6 22 33 222 3334 | 4or5 7979 or 8 9 8 9 7 22 33 999 99 99 | Blank null  

A new table should be prepared, where the result is added to the previous call of my . Any help is appreciated thanks in advance.

This solution is a little complex, but it is a starting point.

First of all, let's create an auxiliary table:

  Create Template Table Temporarily Choose MID Middle, LID Lid, ABS (Lstem- M.Stime) m Zone Lookup Were M. Route 1 = L. Route 1 and M. Route 2 = L. Rout 2 and M.UTC = L.T.C. And MID & lt; & Gt; L.id;  

From this table, we present the minimum time-feed for each my.id :

  Can get it.  

Result: Central MTMDIF ---------- - - ------ 2 0 5 2 6 1

Now we can find which rows are in lookup stime id :

  SELECT t.mid middle, minimum (lid) lid to temp t zone (SELECT mid, min (timediff) mtimediff FROM TAP GROUP CENTRAL) MT ON TMID = MTMID AND TTEMDF = TM by MTMMTMDiff Group ED  

This is the result:

  middle lid ---------- ---------- 2 1 5 3 6 4  

And finally we use those id s to extract data from the table:

  SELECT m.id, m.route1, m.route2, m.utc, m.stime, l.id, l.time to my mJ JOIN lookup zone (select TMM mid, mid (lid) From the lid temp t zone (Select Mid, Min (Timerife) MTMIDF from Temple Group MIT ON TMID = MTMID and T.T.M.IDF = MTMMTMDF Group TMID) MED = Mid & L.D = Lid;  

Give:

  ID route 1 route 2 UTC stem ID ATM ------------------ - ---------- ---------- ---------- ---------- -------- - 2 11 22 111 1111 1 7777 5 22 33 222 2220 3 99 99 6 22 33 222 3334 4 7979