title: SQL SELECT JOIN and DB Architecture
body: <p>As I cannot mange to build a join for getting expected results I started thinking the whole architecture may be wrong.</p>
  
  <p>Models (relevant fields only):</p>
  
  <pre><code>public class AspNetUsers // this is ASPNET default identity table modified
      {
          public string Id { get; set; }
          public int GeoID { get; set; } // FK to GeoData PK
          public string Email { get; set; }
  
  public partial class Product
      {
          public int ID { get; set; }
          public string Name { get; set; }
          public string Description { get; set; }
          public int CategoryID { get; set; } // FK to Category PK
          public string UserID { get; set; } // FK to AspNetUsers PK
  
   public partial class Category
      {
          public int ID { get; set; }
          public string Name { get; set; }
  
   public class GeoData
      {
          public int ID { get; set; }
          public DbGeography GeoLocation { get; set; }
  
  public class WishList
      {
          public int ID { get; set; }
          public string userEmail { get; set; } // FK to AspnetUsers 'Email'
          public int frequency { get; set; }
          public int category { get; set; } // FK to Category PK
          public int range { get; set; }
          public int geoid { get; set; } // FK to Geodata PK
  </code></pre>
  
  <p>The idea is: we have products and categories. Products are user's related. The userID FK in Products model accounts for that, linking each Product to its owner in the AspNetUsers table as every User registers, logs in ecc... through aspnet identity.
  Before setting their products Users must geolocate themself so that their products can be conveniently geolocated (searched) by other users.
  The Geodata table accounts for worldwide coordinates, postal codes, placenames ecc..
  Now to the WishList. Every user may set up to 'n' wishlists setting a category they're interested in, plus a location and a range from that location. 
  Whislists' result is being emailed by sql server to users, scheduled upon 'frequency' field.</p>
  
  <p>I am not sure this is the best possible db architecture. Sometimes you just start with some building blocks (AspNetUsers, Products, Categories), adding other functionality time by time.
  Anyway.. here's a basic SELECT to build the emails to be sent depending on users' different wishlists:</p>
  
  <pre><code>DECLARE C1 CURSOR READ_ONLY
  FOR
  SELECT [userEmail], [frequency], [category],[range], w.[geoid], [searchCity], u.UserName, g.[GeoLocation]
  FROM WishLists as w
  JOIN AspNetUsers as u ON w.userEmail = u.Email
  JOIN GeoData_IT as g ON g.ID = w.geoid
  
  OPEN C1;
      FETCH NEXT FROM C1 INTO
      @userEmail, @frequency, @category, @range, @geoid, @searchCity, @userName, @GeoLocation
      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF @geoid &gt; 0
              BEGIN
                  SELECT p.ID, c.Name, p.Name, g.PlaceName
                            FROM WishLists as w
                            RIGHT OUTER JOIN GeoData_IT AS g ON w.geoid = g.ID
                            JOIN AspNetUsers AS u ON g.ID = u.GeoID
                            JOIN Products as p on u.Id = p.UserID
                            JOIN Categories AS c ON p.CategoryID = c.ID
                            WHERE g.GeoLocation.STDistance(@GeoLocation) &lt;= (@range*5000)
  
              END
              FETCH NEXT FROM C1 INTO
              @userEmail, @frequency, @category, @range, @geoid, @searchCity, @userName, @GeoLocation
      END
  
  CLOSE C1;
  DEALLOCATE C1;
  GO
  </code></pre>
  
  <p>I am not even introducing the 'category' complication at this point, just checking whether wishlist contains a relevant geoid (if geoid >0) trying to extract, for each wishlist, any product belonging to users whose geolocation is in the given range from the given geoid.
  Yet I am getting some duplicated result, randomly on certain wishlist only, and I cannot figure out why.</p>
  
  <p>This looks fine:</p>
  
  <pre><code>1   Nursery Loved Crib Bale Genoa
  2   Baby products   Crib    Genoa
  3   Baby products   Cot Bed Genoa
  4   Feeding Circus Crib Bale    Genoa
  </code></pre>
  
  <p>While this shows duplicates:</p>
  
  <pre><code>1555    Baby products   this  uaga product  Recco
  1555    Baby products   this  uaga product  Recco
  1556    Automotive  uaga product    Recco
  1556    Automotive  uaga product    Recco
  </code></pre>
  
  <p>Is the entire architecture failing or just the SELECT?</p>