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)
LCCRITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Once again, as stated in some other question a bit of relevant data would be nice to have.

It is not quite clear to me what you want to achieve, however, the way you have used the max function, is not correct, is what I believe. So please  try to put your intention in bit more detail.

As I don't know how fluent you are in T-SQL, please allow me state the following:

if you have rows as follows

col1 col2 col3
1 2 3
2 1 2
3 3 1

You are going to get 3 in all columns. I think what you want is first determine the max of one column (say col1) and get corresponding values of col2 and col3. If yes, that is NOT going to happen the way you have written the query.

Please enlighten me!
0
chaauCommented:
You need to add the condition for the mailing into your subqueries as well:
pa.typeid = (Select top 1 typeid from [PlayerManagement].[dbo].playeraddress
where [PlayerManagement].[dbo].playeraddress.playerid = pa.playerid and [PlayerManagement].[dbo].playeraddress.mailing='Y')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nitin SontakkeDeveloperCommented:
In any typical business application, it would be pretty much impossible to get max of a line1, line2, state, city and zip of a address because the result of such a query would be inherently useless.
0
Nitin SontakkeDeveloperCommented:
@Chaau, and still it is better to determine that id up-front, put it in a variable and just use that variable in the final query. I think!

@LCCRIT, better write a block of script with local variables instead of trying to do everything in just one statement.
0
LCCRITAuthor Commented:
I solved this by removing the Sub query and adding this line to my select statement

MAX(ISNULL(CAST (CASE WHEN pa.Mailing = 'Y' THEN pa.Line1 WHEN pa.Mailing = 'N' THEN pa.Line1 ELSE NULL END AS VARCHAR), 0)).

chaau's solution also worked.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.