daily pastebin goal
9%
SHARE
TWEET

Untitled

a guest Feb 20th, 2019 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. id    name
  2. 1     Grove house
  3. 2     howard house
  4. 3     sunny side
  5.    
  6. prop_id    name
  7. 1          Wifi
  8. 1          Enclosed garden
  9. 1          Swimming pool
  10. 2          Swimming pool
  11.    
  12. SELECT * FROM properties WHERE id in (
  13. select prop_id from advanced_options where name = 'Within 2 miles of sea or river' and prop_id in (
  14.     select prop_id from advanced_options where name = 'WiFi' and prop_id in (
  15.         select prop_id from advanced_options where name = 'Walking distance to pub' and prop_id in (
  16.             select prop_id from advanced_options where name = 'Swimming pool' and prop_id in (
  17.                 select prop_id from advanced_options where name = 'Sea or River views' and prop_id in (
  18.                     select prop_id from advanced_options where name = 'Pet friendly' and prop_id in (
  19.                         select prop_id from advanced_options where name = 'Open fire, wood burning stove or a real flame fire-place' and prop_id in (
  20.                             select prop_id from advanced_options where name='Off road parking')
  21.                         )
  22.                     )
  23.                 )
  24.             )
  25.         )
  26.     )
  27. )
  28.    
  29. INSERT INTO `advanced_options` (id, name)
  30. VALUES
  31.  (1, 'Wifi'),
  32.  (2, 'Enclosing Garden'),
  33.  (8, 'Swimming Pool'),
  34.  (16, 'Grill');
  35.    
  36. Wifi + Swimming Pool = 1 + 8 = 9
  37.    
  38. SELECT * FROM `properties` WHERE `advanced_options` & 9 = 9
  39.    
  40. SELECT * FROM `properties` WHERE `advanced_options` & 8 = 8
  41.    
  42. |prop_id  | wifi |  swimming_pool  |  etc..
  43. -----------------------------------
  44. |   1     |  0   |        1        |
  45. |   2     |  1   |        0        |
  46.    
  47. SELECT * FROM properties AS p
  48. INNER JOIN property_options AS po ON p.id = po.prop.id
  49. WHERE wifi = 1 AND swimming_pool = 1 ....
  50.    
  51. select o1.prop_id
  52. from advanced_options o1
  53. inner join advanced_options o2 on o1.prop_id = o2.prop_id and o2.name = "WiFi"
  54. where o1.name = 'Within 2 miles of sea or river'
  55.    
  56. select p.*,count(a.prop_id) as cnt
  57. from properties p
  58. inner join advanced_options a on a.prop_id = p.id
  59. where a.name in ('Enclosed garden','Swimming pool')
  60. group by p.name
  61. having cnt = 2
  62.    
  63. SELECT * FROM properties WHERE id in (select prop_id from advanced_options where name in ('Within 2 miles of sea or river'.'WiFi','Walking distance to pub','Swimming pool','Sea or River views','Pet friendly','Open fire, wood burning stove or a real flame fire-place','Open fire, wood burning stove or a real flame fire-place'))
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top