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
**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.
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
CASE WHEN R1.State=R2.TState and Count(R2.OrderId)=1
WHEN R1.State=R2.TState and R1.County=R2.County and Count(R2.OrderId)=1
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.