Hi
I have created 2 queries - one with an inner join and one without an inner join
I don't know what Ive done wrong, but when I add in the inner join, my result set is incorrect
Query 1 - without inner join:
SELECT
CON.new_MembershipNumber AS 'Member ID',
CON.new_VIPContactType AS 'Contact Type',
CON.AccountIdName AS 'Company',
CON.FirstName AS 'First Name',
CON.MiddleName AS 'Middle Name',
CON.LastName AS 'Last Name',
CONVERT(varchar,CON.BirthDate,101) AS 'DOB',
CON.GenderCode AS 'Gender',
CON.new_VIPUserType AS 'User Type',
CON.EMailAddress1 AS 'E-mail',
CON.FullName AS 'Member Name',
CON.new_VIPPlanType AS 'Plan Type',
CON.new_VIPProductName AS 'Product Name',
CONVERT(varchar,CON.new_VIPEffectiveDate,101) AS 'Effective Date',
CON.new_address1_province AS 'Location'
FROM dbo.Contact CON
WHERE CON.new_VIPContactType = 'VIP Member'
AND CON.StatusCode = 1
when I run this query I get these results: (1882 row(s) affected)
which is correct
Query 2 - with inner join
SELECT
CON.new_MembershipNumber AS 'Member ID',
CON.new_VIPContactType AS 'Contact Type',
CON.AccountIdName AS 'Company',
CON.FirstName AS 'First Name',
CON.MiddleName AS 'Middle Name',
CON.LastName AS 'Last Name',
CONVERT(varchar,CON.BirthDate,101) AS 'DOB',
CON.GenderCode AS 'Gender',
CON.new_VIPUserType AS 'User Type',
CON.EMailAddress1 AS 'E-mail',
CON.FullName AS 'Member Name',
CON.new_VIPPlanType AS 'Plan Type',
CON.new_VIPProductName AS 'Product Name',
CONVERT(varchar,CON.new_VIPEffectiveDate,101) AS 'Effective Date',
CON.new_address1_province AS 'Location'
dbo.StringMap.Value AS 'Location Value'
FROM dbo.Contact CON
INNER JOIN dbo.StringMap SMB
ON CON.new_address1_province = SMB.AttributeValue
WHERE SMB.AttributeName = 'new_address1_province'
AND CON.new_VIPContactType = 'Advica Member'
AND CON.StatusCode = 1
when I run this query I get these results: (11292 row(s) affected)
which is incorrect