Link to home
Start Free TrialLog in
Avatar of Paula DiTallo
Paula DiTalloFlag for United States of America

asked on

LEFT OUTER JOIN: Filtering out too many rows.

Techies--

This SQL Statement does not do the LEFT OUTER JOIN on ctfax, cdtfax as I thought it would. The LEFT OUTER JOIN works for the physical address. Somehow when I go to grab the fax data, the results look like some equals condition was met--possibly @ the WHERE clause.   From an academic perspective, my thinking regarding the  last 3 filtering conditions under the WHERE is that this is done after the broader results from the LEFT OUTER JOIN take place before shipping the final result set. If that isn't true, please let me know how its really working.
SELECT 
  s.SiteNumber as 'StoreNumber',
  s.SiteName as 'StoreName',
  d.DistrictNumber as 'DistrictNumber',
  f.Number as 'FMUNumber',
  a.Address1,
  a.Address2,
  a.City,
  a.State,
  a.Zip as 'ZipCode',
  --as 'StoreManager',
  --as 'Phone',
  cfax.ContactData as 'Fax',
  sc.ContactID 
 FROM Facility f,
      District d, 
      [Site] s	  -- // Sites may, or may not have associated Address or Fax.
       LEFT OUTER JOIN SiteAddress sa    -- // Look for associated address
        ON s.SiteID = sa.SiteID
         LEFT OUTER JOIN [Address] a
          ON sa.AddressID = a.AddressID
           LEFT OUTER JOIN AddressType at
            ON a.AddressTypeID = at.AddressTypeID
         LEFT OUTER JOIN SiteContact sc   -- // Look for associated Fax#
          ON s.SiteID = sc.SiteID
           LEFT OUTER JOIN  Contact cfax
            ON sc.ContactID = cfax.ContactID 
             LEFT OUTER JOIN ContactType ctfax
              ON cfax.ContactTypeID = ctfax.ContactTypeID 
               LEFT OUTER JOIN ContactDataType cdtfax 
                ON ctfax.ContactDataTypeID = cdtfax.ContactDataTypeID  
  WHERE s.DistrictID = d.DistrictID
    AND s.SiteID = f.SiteID
    AND (at.AddressTypeName = 'Physical' OR at.AddressTypeName IS NULL)
    AND (ctfax.ContactTypeName = 'Fax' OR ctfax.ContactTypeName IS NULL)
    AND (cdtfax.ContactDataTypeName = 'Phone' OR cdtfax.ContactDataTypeName IS NULL)
 ORDER BY s.SiteNumber

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Paula DiTallo

ASKER

Thanks Angel III for confirming what happens @ the WHERE clause level.

...this statement yields the exact same answer...  oh to find the guilty table on a Friday afternoon...
You're really much better off specifying the JOIN criteria in the JOIN clauses.
SELECT 
  s.SiteNumber as 'StoreNumber',
  s.SiteName as 'StoreName',
  d.DistrictNumber as 'DistrictNumber',
  f.Number as 'FMUNumber',
  a.Address1,
  a.Address2,
  a.City,
  a.State,
  a.Zip as 'ZipCode',
  --as 'StoreManager',
  --as 'Phone',
  cfax.ContactData as 'Fax',
  sc.ContactID 
 FROM [Site] s
     INNER JOIN Facility f ON s.SiteID = f.SiteID
     INNER JOIN District d ON s.DistrictID = d.DistrictID
     -- // Sites may, or may not have associated Address or Fax.
        LEFT OUTER JOIN SiteAddress sa    -- // Look for associated address
        ON s.SiteID = sa.SiteID
         LEFT OUTER JOIN [Address] a
          ON sa.AddressID = a.AddressID
           LEFT OUTER JOIN AddressType at
            ON a.AddressTypeID = at.AddressTypeID AND at.AddressTypeName = 'Physical'
         LEFT OUTER JOIN SiteContact sc   -- // Look for associated Fax#
          ON s.SiteID = sc.SiteID
           LEFT OUTER JOIN  Contact cfax
            ON sc.ContactID = cfax.ContactID AND ctfax.ContactTypeName = 'Fax'
             LEFT OUTER JOIN ContactType ctfax
              ON cfax.ContactTypeID = ctfax.ContactTypeID 
               LEFT OUTER JOIN ContactDataType cdtfax 
                ON ctfax.ContactDataTypeID = cdtfax.ContactDataTypeID AND cdtfax.ContactDataTypeName = 'Phone'
ORDER BY s.SiteNumber

Open in new window