Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT event.id_event, event_title, event_details, event_date_time, event_showtime.id_show, event_category.id_category, ( 6371 * ACOS( COS( RADIANS( '49.20513921227407' ) ) * COS( RADIANS( event_showtime.latitude ) ) * COS( RADIANS( event_showtime.longitude ) - RADIANS( '18.762441839599678' ) ) + SIN( RADIANS( '49.20513921227407' ) ) * SIN( RADIANS( event_showtime.latitude ) ) ) ) AS distance
- FROM event
- JOIN event_showtime ON event.id_event = event_showtime.id_event
- JOIN event_category ON event.id_category = event_category.id_category
- HAVING distance < '5'
- ORDER BY distance
- LIMIT 0 , 20
- id_event event_title event_details event_date_time id_show id_category distance
- 1 Testovacia na FRI Testovacia udalosť na FRI 2014-04-01 20:00:00 2 1 0.390327906350887
- 1 Testovacia na FRI Testovacia udalosť na FRI 2014-03-31 16:13:17 1 1 0.390327917895938
- 2 Čin-Čin Čin-Čin je nádherný poetický príbeh o dospievaní. ... 2014-03-31 15:00:00 3 2 1.02364018489261
- INSERT INTO `event_showtime` (`id_show`, `id_event`, `latitude`, `longitude`, `event_date_time`) VALUES
- (1, 1, 49.2016762922894, 18.7615620750428, '2014-03-31 16:13:17'),
- (2, 1, 49.2016762922894, 18.7615620750428, '2014-04-01 20:00:00'),
- (3, 2, 49.2113914818564, 18.7520992416382, '2014-03-31 15:00:00'),
- (4, 2, 49.0545135142313, 20.2952223676682, '2014-04-16 11:00:00'),
- (5, 2, 49.2113914818564, 18.7520992416382, '2014-04-23 11:00:00'),
- (6, 2, 49.0545135142313, 20.2952223676682, '2014-04-30 11:00:00'),
- (7, 2, 49.2016762922894, 18.7615620750428, '2014-04-29 12:00:00'),
- (8, 1, 49.2016762922894, 18.7615620750428, '2014-04-24 12:00:00');
- SELECT id_event , event_title, event_details, event_date_time, id_show, id_category, distance FROM(
- SELECT event.id_event id_event, event_title, event_details, max(event_date_time) event_date_time, max(event_showtime.id_show) id_show, event_category.id_category id_category, ( 6371 * ACOS( COS( RADIANS( '49.20513921227407' ) ) * COS( RADIANS( event_showtime.latitude ) ) * COS( RADIANS( event_showtime.longitude ) - RADIANS( '18.762441839599678' ) ) + SIN( RADIANS( '49.20513921227407' ) ) * SIN( RADIANS( event_showtime.latitude ) ) ) ) AS distance
- FROM event
- JOIN event_showtime ON event.id_event = event_showtime.id_event
- JOIN event_category ON event.id_category = event_category.id_category
- GROUP BY event.id_event
- HAVING distance < '5'
- ORDER BY distance
- LIMIT 0 , 20
- )t
- SELECT event.id_event, event_title, event_details, event_date_time, event_showtime.id_show, event_category.id_category, ( 6371 * ACOS( COS( RADIANS( '49.20513921227407' ) ) * COS( RADIANS( event_showtime.latitude ) ) * COS( RADIANS( event_showtime.longitude ) - RADIANS( '18.762441839599678' ) ) + SIN( RADIANS( '49.20513921227407' ) ) * SIN( RADIANS( event_showtime.latitude ) ) ) ) AS distance
- FROM event
- JOIN event_showtime ON event.id_event = event_showtime.id_event
- JOIN event_category ON event.id_category = event_category.id_category
- HAVING distance < '5'
- ORDER BY event_date_time DESC
- LIMIT 1
- SELECT event.id_event, event_title, event_details, event_date_time
- FROM event
- JOIN event_showtime ON event.id_event = event_showtime.id_event
- JOIN event_category ON event.id_category = event_category.id_category
- JOIN (SELECT MAX(event_date_time) as dt, id_event from event) inner_t
- ON inner_t.id_event = event.id_event
- AND inner_t.dt = event.event_date_time
- WHERE distance < '5'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement