[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

help with select statement condition

Posted on 2013-05-17
12
Medium Priority
?
275 Views
Last Modified: 2013-05-31
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"
0
Comment
Question by:arthurh88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39175846
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

0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39175847
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39175849
SELECT     UserID,
AddressNumber, Address, City, State
FROM         MoreAddresses
WHERE     (UserID = 14920
and addressnumber = (select max (addressnumber) from MoreAddresses where userid = 14920)
)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:arthurh88
ID: 39175896
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' )
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39175914
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
0
 

Author Comment

by:arthurh88
ID: 39175932
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.
0
 

Author Comment

by:arthurh88
ID: 39175940
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"
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39175970
if the address number is variable then you can use the same query above

declare @addressNumber int
set @addressNumber = 7
SELECT     m0.UserID, isnull(mx.Address,m0.Address), isnull(mx.City,m0.City), isnull(mx.State,m0.State)
FROM        MoreAddresses M0
left join	MoreAddresses Mx
on			m0.userID = mx.userid
and			m0.AddressNumber = 0
and			mx.AddressNumber = @addressNumber 

Open in new window


now in the above scenario, it is taking 7 as a value to the variable, you can change it to any number as desire or even put it in a sp and send it as a parameter.
and it will return you the value for the address number if it exists or else for for 0.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39176035
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39177307
where AddressNumber = coalesce(@AddressNumber,0)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39177310
Scratch that, I misunderstood the requirement.
0
 

Author Closing Comment

by:arthurh88
ID: 39210181
thank you, sorry it took so long to update, but it worked excellent.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question