[SOLUTION] Subquery in the where statement always shows up null in the result

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)

pa.typeid = (Select top 1 typeid from [PlayerManagement].[dbo].playeraddress where [PlayerManagement].[dbo].playeraddress.playerid = pa.playerid and [PlayerManagement].[dbo].playeraddress.mailing='Y')

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.

LVL 13

Nitin SontakkeDeveloperCommented: 2017-03-30

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!

LVL 25

chaauCommented: 2017-03-30

You need to add the condition for the mailing into your subqueries as well:

Open in new window

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

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.

@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.

LCCRITAuthor Commented: 2017-03-30

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!

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

Who is Participating?