title: Select rows where no record exists for past 100 years
body: <p>I'm sorry the title isn't very good but I didn't know how to describe my problem properly</p>
  
  <p>I'm trying to get a table with city names that had a disaster at least 100 years from now and since that disaster there weren't any more disaster in this city at least 100 years</p>
  
  <p>so my code works but when there is a city with only one disaster in my Disaster table the city wont appear(even if he was in 1800 for example)</p>
  
  <pre><code>select D1.cname,D1.etype,D1.dyear
  from disaster as D1 , disaster as D2
  where(D1.dyear&lt;=(date_part('year',current_date)-100)) and((D2.cname=D1.cname)and ((D2.dyear&gt;(D1.dyear+100))))                                                                                     
  group by D1.cname,D1.dyear
  </code></pre>
  
  <p>this is my data base</p>
  
  <p><img src="https://i.stack.imgur.com/RcLGw.png" alt="enter image description here"></p>
  
  <p>and this is what I'm getting from my query</p>
  
  <p><img src="https://i.stack.imgur.com/bFsWa.png" alt="enter image description here"></p>
  
  <p>im missing the cities "Tsfat" and "Kagoshima"</p>
  
  <p>Im looking for your help Thanks!</p>
  
  <p>with the help of @Zaynul Abadin Tuhin after the join but still dont display cities with only one disaster 
  <img src="https://i.stack.imgur.com/Efk00.png" alt="enter image description here"></p>