?
Solved

help with select statement condition

Posted on 2013-05-17
12
Medium Priority
?
274 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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