• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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

0
Paula DiTallo
Asked:
Paula DiTallo
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are right that WHERE clause is happening after all the JOIN..

what I see is a JOIN missing, aka I would write the query like this:
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    
 JOIN Facility f
   ON s.SiteID = f.SiteID
 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
         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 (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

0
 
Paula DiTalloIntegration developerAuthor Commented:
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...
0
 
Scott PletcherSenior DBACommented:
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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now