Avatar of LCCRIT
LCCRIT
 asked on

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)
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
LCCRIT

8/22/2022 - Mon
Nitin Sontakke

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!
ASKER CERTIFIED SOLUTION
chaau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Nitin Sontakke

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.
Nitin Sontakke

@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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
LCCRIT

ASKER
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!