CASE STATEMENT RETURNING MULTIPLE ROWS

Question

CASE STATEMENT RETURNING MULTIPLE ROWS

sqlquestions6

SSC Veteran

Points: 296
More actions
October 29, 2013 at 7:44 am

#278640

I am struggling with a case statment for my query. I have Main Address Table and Related Addresses table, Related Table Orders table and Orders table. The Orders table is the main table and it contains the OrderId. The Main Address Table is contains a record for each OrderId. The Related Addresses table contains multiple addresses for each address in the Main Address Table. The Related Address and Main Address table are not related by a common column. The Related Orders Table has got each record for each OrderId in Orders Table. And in Related Orders table, there are ReId for each Order ID. Below are tables for easy understanding. THIS QUESTION IS FOR SQL SERVER 2008 R2
Orders
------------------------
**OrderId**| some other columns not needed here|
Main Address Table
---------------------------
**OrderId** | Address| City | State | Zip| County|
Related Table Orders
---------------------------
**OrderId** | **ReId**
Related Address Table
---------------------
UniqueId| **ReId**| Address |City | State | Zip| County|
My condition is, The [Orders] Table contains an OrderId and I have to pick that OrderId and go to the Main Address Table and get the address, city, county etc. On the other hand, I also need to go to the [Related Table Orders] Table and get the ReId for each OrderID and then get the related property for that ReId in the [Related Address Table]. The problem here is, there are mutiple addresses for each ReId in this table and I need only one of them. The realted addresses could be any address in the united states but while picking, this is what I need to follow
If the related address is in the same state as the main address(based on OrderId), and there is only one such address among the mutiple addresses, then pick that one and how in the result.
If there are dups in the same state, then narrow it down by county, that mean, if there are multiple in the same state as teh main address but only one is the same county as main address, then show that one.
If none of the above conditions satisfy, or if there are dups even in county or if the State field is NULL(was not recorded for some reason for soem properties), then in that case, just get all the related addresses for that OrderId,and ReId and Order them by system assinged Unique Number and pick the first one.
# TEMP
-------------
OrderId | ReId | UniqueId | TAddress| TState | TCounty
ReId is not unique per Related Address. For each OrderId, there is only one ReId but for each ReId, there could be multiple addresses. the only thing that is unique for related addresses is the UniqueId. I know you might be thinking, why put OrderId and then also ReId then, cant you just put OrderId and relate it. Its so simple. I understand, but for some reason, our database guys did it this way. (Or may be it is used for some other reason I am not aware of).
My problem here is, I am writing a case statement and it is returning multiple rows instead of just one and I am not sure why. What I did was, I created a temp table combining [Related Address Table] and [Related Table Orders] joinng them on ReId. Now, I just joined the [Orders ], Main Address Table and the temp table and checked the [State] and [County] of Main Address and Related address based on a case statement. Below is the case statement, this one is for
SELECT
CASE WHEN R1.State=R2.TState and Count(R2.OrderId)=1
THEN R2.Address
WHEN R1.State=R2.TState and R1.County=R2.County and Count(R2.OrderId)=1
THEN R2.Address
ELSE (SELECT TOP1 R2.Address from #Temp Order By R2.UniqueId asc)
END AS AddRelatedProperty
FROM #TEMP R2
left join [Main Address Table] R1 on R1.OrderId=R2.OrderId
Group by R1.State, R2.TState, R2.UniqueId, R2.County, R1.County
The result is, county names from all the related properties displayed in the AddRelatedProperty column.In this case, this OrderId has got 3000 properties and it shows all 3000 rows instead of just one. What am I doing wrong. ?I checked and know for sure that the conditions 1 and 2 are will faile as there are dups in state and county in this case and it should go to the else statment and order by UniqueId and show only one. but that doesnt seem to happen. By the way, i tried with just inner join also, instead of left. It still doesnt seem to work. How can i solve this?Please let me know.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 1

Hi, and welcome to the SSC forums. You will be much more likely to get help with your problem if you post DDL (e.g., your table definitions) and sample data (that other posters can use to test their suggestions). This article explains how:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Also, post an example of your desired result using the sample data you provide.

One more tip - break up the massive wall of text into smaller paragraphs. It will be much easier for other posters to read and understand.

Jason Wolfkill

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

The case expression does not limit the rows that are returned, it is used to determine the value for a given column. If you want to eliminate duplicates a case expression is not going to help.

As previously mentioned we need some more information to help:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlquestions6 SSC Veteran Points: 296 More actions · Answer 3

Thanks for the reply.

I solved the problem myself.

I created three temp tables for each condition using the row over partition and then joined them with the main order table to get the expected results.I will follow the steps as suggested from next time while posting my question. Thanks all for your replies.

Thanks