need help in building this view, please Edited to add results of trying RIGHT OUTER JOIN

Michael J Eber

I have three primary tables: CustomerMaster ContactMaster AddressList Each customer master has a one to many relationship with contacts. Each contact has a possible 1:1 relationship with the AddressList. (ie. null addresses ok) Each address in the AddressList has a 1:1 relationship with CityEnumerators and StateEnumerators. I'm trying to get a view working that shows all contacts that are active whose CustomerMaster record is active. Unfortunately I only get the contacts that have addresses and not the ones without addresses. I thought the following query would do it....but no go. (BTW also tried RIGHT OUTER JOIN with no luck also) Any help would be appreciated as to what I'm doing that is screwing the pooch.

SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity INNER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity INNER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey

Mycroft Holmes

Try changing the 2 inner joins after the left join to outer joins. Remove the order by to your calling method, this gets rid of the top 100% as well.

Never underestimate the power of human stupidity RAH

Michael J Eber

This is my latest attempt and I still am not getting contacts who do not have addresses:

SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity RIGHT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey

i j russell

Did you even bother to read the previous answer from Mycroft?

SELECT dbo.CustomerMaster.BSACustomerKey,
dbo.ContactMaster.ContactName,
dbo.ContactMaster.BusinessPhone,
dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email,
dbo.ContactMaster.FirstName,
dbo.ContactMaster.LastName,
dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance,
dbo.ContactMaster.NetworkAbuse,
dbo.ContactMaster.IssuedSwipeCard,
dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1,
dbo.AddressList.CustomerAddressLine2,
dbo.AddressList.CustomerAddressLine3,
dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City,
dbo.StateEnumerator.State,
dbo.AddressList.CustomerCounty,
dbo.AddressList.CustomerCountry,
dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey
FROM dbo.ContactMaster
INNER JOIN dbo.CustomerMaster
ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity
LEFT OUTER JOIN dbo.AddressList
ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity
LEFT OUTER JOIN dbo.CityEnumerator
ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity
LEFT OUTER JOIN dbo.StateEnumerator
ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE dbo.CustomerMaster.CustomerStatus = 1
AND dbo.ContactMaster.Status = 1
ORDER BY dbo.CustomerMaster.BSACustomerKey ASC