Solved

help with select statement condition

Posted on 2013-05-17
12
267 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
  • 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 40

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 40

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 500 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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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