Advertisement
Guest User

Untitled

a guest
Apr 24th, 2014
40
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.36 KB | None | 0 0
  1. 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
  2. FROM event
  3. JOIN event_showtime ON event.id_event = event_showtime.id_event
  4. JOIN event_category ON event.id_category = event_category.id_category
  5. HAVING distance < '5'
  6. ORDER BY distance
  7. LIMIT 0 , 20
  8.  
  9. id_event event_title event_details event_date_time id_show id_category distance
  10. 1 Testovacia na FRI Testovacia udalosť na FRI 2014-04-01 20:00:00 2 1 0.390327906350887
  11. 1 Testovacia na FRI Testovacia udalosť na FRI 2014-03-31 16:13:17 1 1 0.390327917895938
  12. 2 Čin-Čin Čin-Čin je nádherný poetický príbeh o dospievaní. ... 2014-03-31 15:00:00 3 2 1.02364018489261
  13.  
  14. INSERT INTO `event_showtime` (`id_show`, `id_event`, `latitude`, `longitude`, `event_date_time`) VALUES
  15. (1, 1, 49.2016762922894, 18.7615620750428, '2014-03-31 16:13:17'),
  16. (2, 1, 49.2016762922894, 18.7615620750428, '2014-04-01 20:00:00'),
  17. (3, 2, 49.2113914818564, 18.7520992416382, '2014-03-31 15:00:00'),
  18. (4, 2, 49.0545135142313, 20.2952223676682, '2014-04-16 11:00:00'),
  19. (5, 2, 49.2113914818564, 18.7520992416382, '2014-04-23 11:00:00'),
  20. (6, 2, 49.0545135142313, 20.2952223676682, '2014-04-30 11:00:00'),
  21. (7, 2, 49.2016762922894, 18.7615620750428, '2014-04-29 12:00:00'),
  22. (8, 1, 49.2016762922894, 18.7615620750428, '2014-04-24 12:00:00');
  23.  
  24. SELECT id_event , event_title, event_details, event_date_time, id_show, id_category, distance FROM(
  25. 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
  26. FROM event
  27. JOIN event_showtime ON event.id_event = event_showtime.id_event
  28. JOIN event_category ON event.id_category = event_category.id_category
  29. GROUP BY event.id_event
  30. HAVING distance < '5'
  31. ORDER BY distance
  32. LIMIT 0 , 20
  33. )t
  34.  
  35. 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
  36. FROM event
  37. JOIN event_showtime ON event.id_event = event_showtime.id_event
  38. JOIN event_category ON event.id_category = event_category.id_category
  39. HAVING distance < '5'
  40. ORDER BY event_date_time DESC
  41. LIMIT 1
  42.  
  43. SELECT event.id_event, event_title, event_details, event_date_time
  44. FROM event
  45. JOIN event_showtime ON event.id_event = event_showtime.id_event
  46. JOIN event_category ON event.id_category = event_category.id_category
  47. JOIN (SELECT MAX(event_date_time) as dt, id_event from event) inner_t
  48. ON inner_t.id_event = event.id_event
  49. AND inner_t.dt = event.event_date_time
  50. WHERE distance < '5'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement