Limit left join to 1

Question

I got a problem with limiting left join, what I want is to every row in first table get only one result from second table.

Here is my code without limiting:

SELECT * FROM
(
((SELECT id,date as end,machine_id,numer FROM `order_log` WHERE typ = 1)STOP    
left join    
(SELECT date as begin, machine_id, numer FROM `order_log` ST WHERE typ = 0 ORDER BY date DESC)START   
ON START.begin < STOP.end AND START.machine_id = STOP.machine_id 
AND START.numer = STOP.numer)
)

I also tried to limit it, but then I get only one correct result:

SELECT * FROM
(
((SELECT id,date as end,machine_id,numer FROM `order_log` WHERE typ = 1)STOP    
left join    
(SELECT date as begin, machine_id, numer FROM `order_log` ST WHERE typ = 0 ORDER BY date DESC)START   
ON START.begin = (SELECT date FROM `order_log` WHERE date < STOP.end AND typ = 0 AND machine_id = STOP.machine_id AND numer = STOP.numer ORDER BY date DESC LIMIT 1)    AND START.machine_id = STOP.machine_id 
AND START.numer = STOP.numer)
)

Below example of table:

id  numer   machine_id  typ     date
1   31392   39          0       2015-05-26 15:44:56
2   31761   23          0       2015-05-26 16:12:53
3   31761   24          0       2015-05-26 16:14:03
4   31591   15          0       2015-05-26 16:15:02
5   31586   40          0       2015-05-26 16:15:46
6   31392   39          1       2015-05-26 16:16:19
7   31392   39          0       2015-05-26 16:16:19
8   31392   39          1       2015-05-28 08:15:26
9   31386   39          0       2015-05-28 08:15:26
10  31761   24          1       2015-06-02 00:40:07
11  31761   24          0       2015-06-02 00:40:07
12  31386   39          1       2015-06-02 13:11:13
13  31392   39          0       2015-06-02 13:11:13

And expected result:

id  end                     machine_id  numer   begin                   machine_id  numer   
6   2015-05-26 16:16:19     39          31392   2015-05-26 15:44:56     39          31392
10  2015-06-02 00:40:07     24          31761   2015-05-26 16:14:03     24          31761
8   2015-05-28 08:15:26     39          31392   2015-05-26 16:16:19     39          31392
12  2015-06-02 13:11:13     39          31386   2015-05-28 08:15:26     39          31386

Thanks in advance

EDIT:

To clarify, my query (1st one) is working but it gives more result than I want, so I need only to limit it to get only one row from 2nd table of left join. So to every row from

(SELECT id,date as end,machine_id,numer FROM `order_log` WHERE typ = 1)

I want to get one and only one row from

left join    
(SELECT date as begin, machine_id, numer FROM `order_log` ST WHERE typ = 0 ORDER BY date DESC)START   
ON START.begin = (SELECT date FROM `order_log` WHERE date < STOP.end AND typ = 0 AND machine_id = STOP.machine_id AND numer = STOP.numer ORDER BY date DESC LIMIT 1)    AND START.machine_id = STOP.machine_id 
AND START.numer = STOP.numer)

I need to get all existing pairs, but I am sure that if end exists begin also exists, that's why I search for all typ='1'(end) first.

Below the result I got right now to compare with expected one:

2015-05-26 16:16:19     39  31392   2015-05-26 15:44:56     39  31392
2015-05-28 08:15:26     39  31392   2015-05-26 15:44:56     39  31392
2015-06-02 00:40:07     24  31761   2015-05-26 16:14:03     24  31761
2015-05-28 08:15:26     39  31392   2015-05-26 16:16:19     39  31392
2015-06-02 13:11:13     39  31386   2015-05-28 08:15:26     39  31386

2nd row is unwanted

Answer 1

First, don't over use subqueries in MySQL. So, write the query as:

SELECT s.id, s.date as end, s.machine_id, s.numer,
       o.date as begin, o.machine_id, o.numer 
FROM order_log s left join
     order_log o
     ON o.date < s.date AND o.machine_id = s.machine_id AND o.numer = s.numer
WHERE s.typ = 1 and o.typ = 0 ;

(I think this is equivalent.) The subqueries add additional overhead for materialization (only in MySQL, other databases do the right thing). And, they tend to prevent the use of indexes.

However, you seem to want the most recent pair of starts and stops. With your data structure, it is a bit painful to get this using joins. Instead, for each row, count the number of stops that appear afterwards for a given machine/numer combination. Then simply take the rows that have a value of "1" and use conditional aggregation:

select machine_id, numer,
       max(case when ol.typ = 1 then id end) as end_id,
       max(case when ol.typ = 1 then date end) as end_date,
       max(case when ol.typ = 0 then id end) as start_id,
       max(case when ol.typ = 0 then date end) as start_date
from (select ol.*,
             (select count(*)
              from order_log ol2
              where ol2.machine_id = ol.machine_id AND ol2.numer = ol.numer
             ) as grp
      from orderlog ol
     ) ol
where grp = 1
group by machine_id, numer;