[sql] Inner where date closest to now and next
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT
ch.id,
ch.name,
ch.name_short,
np.titel np_titel,
np.datum_start np_start,
np.datum_end np_end,
ne.titel ne_titel,
ne.datum_start ne_start,
ne.datum_end np_end
FROM
channels ch
LEFT JOIN
programs np
ON ch.id = np.channel_id AND NOW() BETWEEN np.datum_start AND np.datum_end
LEFT JOIN
(SELECT
channel_id,
p.titel,
datum_start,
datum_end
FROM
(SELECT
channel_id,
MIN(datum_start) datum_start,
MIN(datum_end) datum_end
FROM
programs
WHERE
datum_start > NOW()
GROUP BY channel_id) c
JOIN
programs p
USING (channel_id, datum_start, datum_end)
) ne
ON ch.id = ne.channel_id
ch.id,
ch.name,
ch.name_short,
np.titel np_titel,
np.datum_start np_start,
np.datum_end np_end,
ne.titel ne_titel,
ne.datum_start ne_start,
ne.datum_end np_end
FROM
channels ch
LEFT JOIN
programs np
ON ch.id = np.channel_id AND NOW() BETWEEN np.datum_start AND np.datum_end
LEFT JOIN
(SELECT
channel_id,
p.titel,
datum_start,
datum_end
FROM
(SELECT
channel_id,
MIN(datum_start) datum_start,
MIN(datum_end) datum_end
FROM
programs
WHERE
datum_start > NOW()
GROUP BY channel_id) c
JOIN
programs p
USING (channel_id, datum_start, datum_end)
) ne
ON ch.id = ne.channel_id
Gewijzigd op 14/05/2014 13:54:38 door Ger van Steenderen
En natuurlijk indexen zetten op de kolommen die in join voorwaarden voorkomen (dit geldt voor beide methodes)
En meten is weten! ;-)