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
UserIDint
AddressIDint
Address
AddressIDint
Address1varchar(100)
Address2varchar(100)
Address3varchar(100)
Address4varchar(100)
Citynvarchar(100)
PostalCodevarchar(10)
Countrysmallint
AddressTypeint
CreateDatesmalldatetime
CreateUserIDint
Updatedatesmalldatetime
UpdateUserIDint
ProvinceTypeint
Inactivebit
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
On 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