Link to home
Create AccountLog in
Avatar of arthurh88
arthurh88

asked on

help with select statement condition

In my MoreAddresses table I have a column called "AddressNumber"
SELECT     UserID, AddressNumber, Address, City, State
FROM         MoreAddresses
WHERE     (UserID = 14920)

Every UserID will at minimum have one record where addressnumber = 0.  Some userID's may or may not have an additional record where addressnumber = 1.

I want my query to return 1 record, using this logic:

 
"If the len(address) >1 (i.e. there is a record there) where adressnumber =1, then show the record with addressnumber =1, otherwise show the record where addressnumber = 0"
Avatar of Surendra Nath
Surendra Nath
Flag of India image

the below query might help

SELECT     m0.UserID, isnull(m1.Address,m0.Address), isnull(m1.City,m0.City), isnull(m1.State,m0.State)
FROM        MoreAddresses M0
left join	MoreAddresses M1
on			m0.userID = m1.userid
and			m0.AddressNumber = 0
and			m1.AddressNumber = 1

Open in new window

Try this

SELECT     
UserID, 
Case
When len(address) >1 then '1' 
Else '0'
End as addressnumber,

Address, City, State
FROM         MoreAddresses
WHERE     (UserID = 14920)

Open in new window

SELECT     UserID,
AddressNumber, Address, City, State
FROM         MoreAddresses
WHERE     (UserID = 14920
and addressnumber = (select max (addressnumber) from MoreAddresses where userid = 14920)
)
Avatar of arthurh88
arthurh88

ASKER

i couldn't quite use any of those.  I should have added that users can also have more than 2 addresses.  Some USERID's may have several Addressnumbers.   So I cannot select MAX because I want Addressnumber = x (as a variable), in the example above, I was just using 1 for simplicity.  

I also can't use Case
When len(address) >1 then '1'  
because of the possibility a user may have several addressnumbers.
Unless we can do something like this?

Case
When len(address) >1 WHERE addressnumber = 1 then '1'    

If we can put that WHERE condition into the case, it would work great.  Then I could literally substitute any number for 1.  (i.e.  When len(address) >1 WHERE addressnumber = 2 then '2' )
if x is going to be a variable:

SELECT     UserID,
AddressNumber, Address, City, State
FROM         MoreAddresses
WHERE    
UserID = 14920
and addressnumber = @addressNumber
)



if you want the default in case of a blank you would something similiar to neo's:

SELECT     m0.UserID,
case when len (m1.Address) > 1 then m1.Address else m0.Address end Address,
--repeat for rest.

FROM        MoreAddresses M0
left join      MoreAddresses M1
on                  m0.userID = m1.userid
and                  m0.AddressNumber = 0
and                  m1.AddressNumber = @AddressNumber
yes, exactly, I want to select a variable addressnumber, but if it is blank, then I want to return where addressnumber = 0.  I'll see if I can get that one to work.
I dont know how to get Neo's to work because for any userID, they could have 1 or 10 addressnumbers.  Would I need to write a query that said:
and                  m0.AddressNumber = 0
and                  m1.AddressNumber = 1
and                  m2.AddressNumber = 2
and                  m3.AddressNumber = 3
and                  m4.AddressNumber = 4
and                  m5.AddressNumber = 5
and                  m6.AddressNumber = 6
and                  m7.AddressNumber =7
and so forth?  I wasn't quite sure I understood right :(    "if it exists where addressnumber = x, then x, otherwise 0"
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
DECLARE @AddressNumber int
SET @AddressNumber = 1

SELECT UserID, AddressNumber, Address, City, State
FROM (
    SELECT     UserID, AddressNumber, Address, City, State,
        ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY AddressNumber DESC) AS row_num
    FROM         dbo.MoreAddresses
    WHERE     (UserID = 14920) --optional
        AND (AddressNumber = 0 OR AddressNumber = @AddressNumber)
) AS derived
WHERE
    row_num = 1
where AddressNumber = coalesce(@AddressNumber,0)
Scratch that, I misunderstood the requirement.
thank you, sorry it took so long to update, but it worked excellent.