• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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