SELECT *
  FROM peatumisajad as p1, peatumisajad as p2
  WHERE p1.peatuse_id=p2.peatuse_id
    AND p1.liini_nr=11;

+----+----------+------------+------------------+----+----------+------------+------------------+
| id | liini_nr | peatuse_id | aeg_algpeatusest | id | liini_nr | peatuse_id | aeg_algpeatusest |
+----+----------+------------+------------------+----+----------+------------+------------------+
|  4 |       11 |          1 | 00:00:00         |  1 |       10 |          1 | 00:00:00         |
|  4 |       11 |          1 | 00:00:00         |  4 |       11 |          1 | 00:00:00         |
|  5 |       11 |          5 | 00:45:00         |  5 |       11 |          5 | 00:45:00         |
|  6 |       11 |          6 | 01:00:00         |  6 |       11 |          6 | 01:00:00         |
|  7 |       11 |          3 | 01:35:00         |  7 |       11 |          3 | 01:35:00         |
|  6 |       11 |          6 | 01:00:00         |  9 |       12 |          6 | 01:15:00         |
|  7 |       11 |          3 | 01:35:00         | 10 |       12 |          3 | 01:55:00         |
+----+----------+------------+------------------+----+----------+------------+------------------+

SELECT DISTINCT p2.liini_nr
  FROM peatumisajad as p1, peatumisajad as p2
  WHERE p1.peatuse_id=p2.peatuse_id
    AND p1.liini_nr<>p2.liini_nr
    AND p1.liini_nr=11;

+----------+
| liini_nr |
+----------+
|       10 |
|       12 |
+----------+



SELECT p2.peatuse_id, p2.liini_nr
  FROM peatumisajad as p1, peatumisajad as p2
  WHERE p1.peatuse_id=p2.peatuse_id
    AND p1.liini_nr=11
    AND p1.liini_nr<>p2.liini_nr;


+------------+----------+
| peatuse_id | liini_nr |
+------------+----------+
|          1 |       10 |
|          6 |       12 |
|          3 |       12 |
+------------+----------+

-- lisage peatuse ja liini nimetused

SELECT peatusenimi, p2.peatuse_id, p2.liini_nr
  FROM peatumisajad as p1, peatumisajad as p2,
       peatused
  WHERE p1.peatuse_id=p2.peatuse_id
    AND p1.liini_nr=11
    AND p1.liini_nr<>p2.liini_nr
    AND p2.peatuse_id=peatused.id;


+-------------+------------+----------+
| peatusenimi | peatuse_id | liini_nr |
+-------------+------------+----------+
| Viljandi    |          1 |       10 |
| Elva        |          6 |       12 |
| Tartu       |          3 |       12 |
+-------------+------------+----------+

SELECT peatusenimi, p2.peatuse_id, p2.liini_nr, liini_nimetus
  FROM peatumisajad as p1, peatumisajad as p2,
       peatused, liinid
  WHERE p1.peatuse_id=p2.peatuse_id
    AND p1.liini_nr=11
    AND p1.liini_nr<>p2.liini_nr
    AND p2.peatuse_id=peatused.id
    AND p2.liini_nr=liinid.liini_nr;


+-------------+------------+----------+---------------------+
| peatusenimi | peatuse_id | liini_nr | liini_nimetus       |
+-------------+------------+----------+---------------------+
| Viljandi    |          1 |       10 | Viljandi-Valga kiir |
| Elva        |          6 |       12 | Karksi-Tartu        |
| Tartu       |          3 |       12 | Karksi-Tartu        |
+-------------+------------+----------+---------------------+

SELECT peatusenimi, liini_nimetus
  FROM peatumisajad as p1, peatumisajad as p2,
       peatused, liinid
  WHERE p1.peatuse_id=p2.peatuse_id
    AND p1.liini_nr=11
    AND p1.liini_nr<>p2.liini_nr
    AND p2.peatuse_id=peatused.id
    AND p2.liini_nr=liinid.liini_nr;


+-------------+---------------------+
| peatusenimi | liini_nimetus       |
+-------------+---------------------+
| Viljandi    | Viljandi-Valga kiir |
| Elva        | Karksi-Tartu        |
| Tartu       | Karksi-Tartu        |
+-------------+---------------------+



SELECT peatuse_id
  FROM peatumisajad
  WHERE peatumisajad.liini_nr=11;


+------------+
| peatuse_id |
+------------+
|          1 |
|          5 |
|          6 |
|          3 |
+------------+



SELECT huviala_id, COUNT(*) as kasutajate_kogus 
  FROM kasutajad_huvialad
  GROUP BY huviala_id;



SELECT huviala, GROUP_CONCAT(knimi) AS kasutajanimed
  FROM kasutajad, kasutajad_huvialad, huvialad
  WHERE kasutajad_huvialad.huviala_id=huvialad.id
    AND kasutajad_huvialad.kasutaja_id=kasutajad.id
  GROUP BY huviala_id;



SELECT peatuse_id, ADDTIME(algusaeg, aeg_algpeatusest)
 FROM reisid, peatumisajad
 WHERE reisid.liini_nr=peatumisajad.liini_nr
   AND reisid.id=1
 ORDER BY aeg_algpeatusest;


+------------+-------------------------------------+
| peatuse_id | ADDTIME(algusaeg, aeg_algpeatusest) |
+------------+-------------------------------------+
|          1 | 13:10:00                            |
|          5 | 13:55:00                            |
|          6 | 14:10:00                            |
|          3 | 14:45:00                            |
+------------+-------------------------------------+


SELECT reisid.liini_nr, ADDTIME(algusaeg, aeg_algpeatusest) as aeg
  FROM peatumisajad, reisid
  WHERE peatumisajad.liini_nr=reisid.liini_nr
   AND peatuse_id=6;


+----------+----------+
| liini_nr | aeg      |
+----------+----------+
|       11 | 14:10:00 |
|       11 | 17:10:00 |
|       11 | 17:10:00 |
|       12 | 19:15:00 |
+----------+----------+

SELECT CONCAT(reisid.liini_nr,' kell ', ADDTIME(algusaeg, aeg_algpeatusest) ) as istumine
  FROM peatumisajad, reisid
  WHERE peatumisajad.liini_nr=reisid.liini_nr
   AND peatuse_id=6;


+------------------+
| istumine         |
+------------------+
| 11 kell 14:10:00 |
| 11 kell 17:10:00 |
| 11 kell 17:10:00 |
| 12 kell 19:15:00 |
+------------------+


SELECT peatuse_id, ADDTIME(algusaeg, aeg_algpeatusest) as aeg,
   GROUP_CONCAT(
    SELECT CONCAT(reisid.liini_nr,' kell ', 
      ADDTIME(algusaeg, aeg_algpeatusest) ) as istumine
  FROM peatumisajad as p2, reisid as r2
  WHERE p2.liini_nr=r2.liini_nr
   AND peatumisajad.peatuse_id=p2.peatuse_id
) as ymberistumised
 FROM reisid, peatumisajad
 WHERE reisid.liini_nr=peatumisajad.liini_nr
   AND reisid.id=1
 ORDER BY aeg_algpeatusest;




SELECT CONCAT(p2.liini_nr,' kell ', 
      ADDTIME(algusaeg, aeg_algpeatusest) ) as istumine
  FROM peatumisajad as p2, reisid as r2
  WHERE p2.liini_nr=r2.liini_nr
   AND 6=p2.peatuse_id


SELECT p1.peatuse_id, p2.liini_nr
 FROM reisid, peatumisajad as p1, peatumisajad as p2
 WHERE 
    p1.peatuse_id=p2.peatuse_id
   AND reisid.liini_nr=p1.liini_nr
   AND reisid.id=1
 ORDER BY p1.aeg_algpeatusest;


+------------+----------+
| peatuse_id | liini_nr |
+------------+----------+
|          1 |       10 |
|          1 |       11 |
|          5 |       11 |
|          6 |       11 |
|          6 |       12 |
|          3 |       11 |
|          3 |       12 |
+------------+----------+


SELECT p1.peatuse_id, p2.liini_nr
 FROM reisid, peatumisajad as p1, peatumisajad as p2
 WHERE 
    p1.peatuse_id=p2.peatuse_id
   AND p1.liini_nr<>p2.liini_nr
   AND reisid.liini_nr=p1.liini_nr
   AND reisid.id=1
 ORDER BY p1.aeg_algpeatusest;

+------------+----------+
| peatuse_id | liini_nr |
+------------+----------+
|          1 |       10 |
|          6 |       12 |
|          3 |       12 |
+------------+----------+

SELECT p1.peatuse_id,
  ADDTIME(reisid.algusaeg, p1.aeg_algpeatusest),
  p2.liini_nr,
 FROM reisid, peatumisajad as p1, peatumisajad as p2
 WHERE 
    p1.peatuse_id=p2.peatuse_id
   AND p1.liini_nr<>p2.liini_nr
   AND reisid.liini_nr=p1.liini_nr
   AND reisid.id=1
 ORDER BY p1.aeg_algpeatusest;


+------------+----------+-----------------------------------------------+
| peatuse_id | liini_nr | ADDTIME(reisid.algusaeg, p1.aeg_algpeatusest) |
+------------+----------+-----------------------------------------------+
|          1 |       10 | 13:10:00                                      |
|          6 |       12 | 14:10:00                                      |
|          3 |       12 | 14:45:00                                      |
+------------+----------+-----------------------------------------------+


SELECT p1.peatuse_id,
  ADDTIME(reisid.algusaeg, p1.aeg_algpeatusest) as ise,
  ADDTIME(r2.algusaeg, p2.aeg_algpeatusest) as istutav,
  p2.liini_nr
 FROM reisid, reisid as r2,
   peatumisajad as p1, peatumisajad as p2
 WHERE 
    p1.peatuse_id=p2.peatuse_id
   AND p1.liini_nr<>p2.liini_nr
   AND reisid.liini_nr=p1.liini_nr
   AND r2.liini_nr=p2.liini_nr
   AND reisid.id=1
 ORDER BY p1.aeg_algpeatusest;


+------------+----------+----------+----------+
| peatuse_id | ise      | istutav  | liini_nr |
+------------+----------+----------+----------+
|          1 | 13:10:00 | 09:15:00 |       10 |
|          6 | 14:10:00 | 19:15:00 |       12 |
|          3 | 14:45:00 | 19:55:00 |       12 |
+------------+----------+----------+----------+

SELECT p1.peatuse_id,
  ADDTIME(reisid.algusaeg, p1.aeg_algpeatusest) as ise,
  ADDTIME(r2.algusaeg, p2.aeg_algpeatusest) as istutav,
  p2.liini_nr
 FROM reisid, reisid as r2,
   peatumisajad as p1, peatumisajad as p2
 WHERE 
    p1.peatuse_id=p2.peatuse_id
   AND p1.liini_nr<>p2.liini_nr
   AND reisid.liini_nr=p1.liini_nr
   AND r2.liini_nr=p2.liini_nr
   AND reisid.id=1
   AND ADDTIME(reisid.algusaeg, p1.aeg_algpeatusest) < 
       ADDTIME(r2.algusaeg, p2.aeg_algpeatusest)
 ORDER BY p1.aeg_algpeatusest;

-- asendage peatuse id peatuse nimega