Cascading address returns in query

I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle