Paula DiTallo
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
...this statement yields the exact same answer... oh to find the guilty table on a Friday afternoon...