Solved

help with select statement condition

Posted on 2013-05-17
12
261 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 39

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
 

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 39

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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:ScottPletcher
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 31

Expert Comment

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

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Contained Database Collations 6 19
t-sql splitting name column 5 23
replication - alerts? 4 20
sql query Help 12 17
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now