Thursday, February 21, 2013 7:25 PM
I have two mailing addresses each with a different purpose. Some have a "M"ailing address...some have a "D"efault address. If a record has both, I want the "M"ailing address to be the survivor. So if I UNION this, and use the "M"ailing address first, will that be the survivor in the UNION if it also finds a "D"efault address for the same individual? Or vice versa? Or can't I control the survivor?
Thanks for your review and am hopeful for a reply?
Thursday, February 21, 2013 7:32 PM
Or can't I control the survivor?
What for a survivor? A UNION select returns distinct values (against a UNION ALL), so it doesn't matter who "survives", it's always the same value you get.
Olaf HelperBlog Xing
- Proposed As Answer by Gert-Jan Strik Thursday, February 21, 2013 8:34 PM
Thursday, February 21, 2013 7:38 PM
declare @t table (sno int,Mcoun varchar(20),Addtype varchar(20)) insert into @t values (1,'USA','Mail'), (1,'AUS','default'), (2,'USA','Mail'), (2,'USA','default'), (3,'USA','Mail'), (4,'AUS','default') select sno ,Mcoun from @t where Addtype= 'Mail' union Select sno , Mcoun from @t A where Addtype = 'Default' and not exists (select sno from @t B where Addtype= 'Mail' and a.Sno=B.Sno )
gets the mailing address instead of default address, if the individual has both address, irrespectivley of whether they are same or not.
If they are same all the time, union would work. As Olaf said, you would not need to care which but I think your requiremtn more like below
if they are or can be different and you want to get mailing address in such case, and only default address, if they do not have mailing address, you can try the code above.
Hope it Helps!!
Thursday, February 21, 2013 7:48 PM
If you give us table definitions and sample data, we could probably do a better job of helping you. My guess is you don't want to do a union at all, but instead you would want something like
Declare @Customers Table(CustomerID int); Declare @Addresses Table(CustomerID int, MailingAddressType char(1), Address varchar(30)); Insert @Customers(CustomerID) Values (1), (2), (3); Insert @Addresses(CustomerID, MailingAddressType, Address) Values (1, 'D', 'New York'), (2, 'M', 'Chicago'), (3, 'D', 'Kansas City'), (3, 'M', 'Atlanta'); ;With cte As (Select c.CustomerID, a.Address, Row_Number() Over(Partition By c.CustomerID Order By a.MailingAddressType Desc) As rn From @Customers c Inner Join @Addresses a On c.CustomerID = a.CustomerID) Select CustomerID, Address From cte Where rn = 1 Order By CustomerID;
- Marked As Answer by Iric WenModerator Friday, March 01, 2013 8:38 AM