Subquery in the where statement always shows up null in the result

Hi Experts,


I have a question about a result. I have a table called player address or “pa”. In this table are multiple rows for addresses that contain an address and a column called mailing which has an N or Y value. What I need is to create logic where if the value of that table in the Mailing column = Y then select the corresponding row in that same table and have  that pa.line1 column appear in this aggegated  result.

The problem is right now because of the select top 1 subquery in the where statement this always shows up with the first row column regardless of the logic I have used. If i use a where to define that mailing = Y then select the corresponding row then it just turns up a NULL value in the resulting column. I just don't know how to put it together.  Here is my code.


SELECT MAX(ISNULL(CAST(p.PlayerID  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Status  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Title_old  AS VARCHAR), 0))
,MAX(ISNULL(CAST(pn.FirstName AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.MiddleName_old  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pn.LastName AS VARCHAR), 0)),
MAX (CAST(CASE WHEN p.DateEnrolled = p.DateEnrolled
THEN CONVERT(VARCHAR(20), p.DateEnrolled, 120)
ELSE CONVERT(VARCHAR(20), p.DateEnrolled, 120)
END AS VARCHAR)),

MAX (CAST(CASE WHEN p.Birthday = p.Birthday
THEN CONVERT(VARCHAR(20), p.Birthday, 120)

ELSE CONVERT(VARCHAR(20), p.Birthday, 120)
END AS VARCHAR)), MAX(ISNULL(CAST(p.AttractionNumber  AS VARCHAR), 0)),



MAX(ISNULL(CAST(p.Exempt  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.EnrollmentSource  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.PinNumber  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.AnniversaryDate  AS VARCHAR), 0)),

MAX (CAST(CASE WHEN p.CurrentDay = p.CurrentDay
THEN CONVERT(VARCHAR(20), p.CurrentDay, 120)
ELSE CONVERT(VARCHAR(20), p.CurrentDay, 120)
END AS VARCHAR)), MAX(ISNULL(CAST(p.CurrentTrip  AS VARCHAR), 0)),


MAX (CAST(CASE WHEN p.CurrentDayBeginDate = p.CurrentDayBeginDate
THEN CONVERT(VARCHAR(20), p.CurrentDayBeginDate, 120)
ELSE CONVERT(VARCHAR(20), p.CurrentDayBeginDate, 120)
END AS VARCHAR)),MAX (CAST(CASE WHEN p.XLastUpdated = p.XLastUpdated  THEN CONVERT(VARCHAR(20),


p.XLastUpdated, 120) ELSE CONVERT(VARCHAR(20), p.XLastUpdated, 120) END AS VARCHAR)),

MAX(ISNULL(CAST(p.AbandonedCard  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Nickname_old  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.ID1  AS VARCHAR), 0)),
MAX(ISNULL(CAST(dmpc.PostalMailOptIn  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.Gender  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.GenerationID  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.CompanyName  AS VARCHAR), 0)),
MAX(ISNULL(CAST(p.JobTitle  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.TypeID  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Line1  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Line2  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.City  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.State  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.ZipCode  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.ZipPlus  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.CountryID  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Mailing  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pa.Bad  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.Phone  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.Preferred  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.AllowMessages  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pp.Extension  AS VARCHAR), 0)),
MAX(ISNULL(CAST(pe.Email  AS VARCHAR), 0)),

MAX(ISNULL(CAST(pe.DefaultEmail  AS VARCHAR), 0)),
MAX(ISNULL(CAST(ph.HostID  AS VARCHAR), 0)),
MAX(ISNULL(CAST(r.description  AS VARCHAR), 0)),
MAX(ISNULL(CAST(rc.description  AS VARCHAR), 0)),
MAX(ISNULL(CAST([PlayerManagement].[dbo].PlayerBalance.SlotPoints  AS VARCHAR), 0)),Sum
(CASE WHEN [PlayerManagement].[dbo].PlayerDayPointsEarn.AccountingDate > = '2016-07-01 00:00:00.000'
THEN [PlayerManagement].[dbo].PlayerDayPointsEarn.Amount  ELSE 0 END)


FROM [PlayerManagement].[dbo].Player p (NOLOCK)LEFT JOIN [PlayerManagement].[dbo].PlayerBalance

on p.PlayerID  = [PlayerManagement].[dbo].PlayerBalance.PlayerID
LEFT JOIN [PlayerManagement].[dbo].PlayerDayPointsEarn on p.PlayerID = [PlayerManagement].[dbo].PlayerDayPointsEarn.PlayerID
LEFT JOIN [PlayerManagement].dbo.PlayerName as pn on p.PlayerID = pn.PlayerID
LEFT JOIN [PlayerManagement].[dbo].PlayerAddress pa (NOLOCK) on pa.playerid = p.playerid

AND (pa.typeid = (Select top 1 typeid from [PlayerManagement].[dbo].playeraddress

where [PlayerManagement].[dbo].playeraddress.playerid = pa.playerid))
LEFT JOIN [PlayerManagement].[dbo].PlayerEmail pe (NOLOCK) on pe.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].PlayerPhone pp (NOLOCK) on pp.playerid = p.playerid
AND (pp.TypeID = (Select TOP 1 TypeID from [PlayerManagement].[dbo].PlayerPhone
where [PlayerManagement].[dbo].playerphone.playerid = pp.playerid))

LEFT JOIN [PlayerManagement].[dbo].PlayerHost ph (NOLOCK) on ph.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].DirectMarketingPlayerConfig dmpc on dmpc.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].PlayerRestrictions pr (NOLOCK) on pr.playerid = p.playerid
LEFT JOIN [PlayerManagement].[dbo].RestrictionCodes rc (NOLOCK) on rc.RestrictionMaskID = pr.RestrictionMaskID
LEFT Join [PlayerManagement].[dbo].PlayerSiteGroup psg (NOLOCK) on psg.playerid = p.playerid

LEFT Join [PlayerManagement].[dbo].Ranking r (NOLOCK) on r.rankingnumber = psg.rankingID



group by (p.PlayerID)