quote:

---

Originally posted by TG

You should have an "association" table between your User_Profiles and Office_locations table. ie:
userLocations (userid, officeid, [locationtypeid?], [isPrimary?], primary key (userid, officeid))

that will be much more efficient. If that is not an option for you there are ways to do it...what version of sql server are you running?

Be One with the Optimizer
TG

---

quote:

---

Originally posted by bielen

I am using SQL Server 2005.

If I have an association table as you describe, can I still do it in a single query or would I have to have a separate query to select all the office records that match the office ID?

quote:

---

Originally posted by TG

You should have an "association" table between your User_Profiles and Office_locations table. ie:
userLocations (userid, officeid, [locationtypeid?], [isPrimary?], primary key (userid, officeid))

that will be much more efficient. If that is not an option for you there are ways to do it...what version of sql server are you running?

Be One with the Optimizer
TG

---

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by bielen

I am using SQL Server 2005.

If I have an association table as you describe, can I still do it in a single query or would I have to have a separate query to select all the office records that match the office ID?

quote:

---

Originally posted by TG

You should have an "association" table between your User_Profiles and Office_locations table. ie:
userLocations (userid, officeid, [locationtypeid?], [isPrimary?], primary key (userid, officeid))

that will be much more efficient. If that is not an option for you there are ways to do it...what version of sql server are you running?

Be One with the Optimizer
TG

---

---

You can still retrieve them in a single query. Filtering on OfficeID will give you all records for particular office.

---

quote:

---

Originally posted by visakh16

Are you using SQL 2005?

---

quote:

---

Originally posted by bielen

quote:

---

Originally posted by visakh16

Are you using SQL 2005?

---

Yes

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by bielen

quote:

---

Originally posted by visakh16

Are you using SQL 2005?

---

Yes

---

What are your current table structures? Is userOfficeIDs field in user_profiles storing comma delimited IDs? ALso you could give some sample data from each table?

---

declare @user_profiles table
(userID int, userFirstName varchar(100), userInitial char(1), UserLastName varchar(100), UserType int, userOfficeIDs varchar(100), userEmail varchar(100))
INSERT INTO @user_profiles
SELECT 1, 'John', 'J', 'Smith', 1, '1', 'emailAddress'
UNION ALL
SELECT 2, 'Mary', 'J', 'Smith', 2, '1,2', 'emailAddress'
UNION ALL
SELECT 3, 'Jack', 'J', 'Smith' ,2, '1,3', 'emailAddress'

SELECT * FROm @user_profiles t
CROSS APPLY ParseValues(t.userOfficeIDs) b

declare @user_types table

(userTypeID int, userTypeDesc varchar(100))
insert into @user_types
SELECT 1, 'Manager'
UNION ALL
SELECT 2, 'Director'
UNION ALL
SELECT 3, 'VP'

declare @office_locations table
(officeID int, 
officeState varchar(100)
)
insert into @office_locations
SELECT 1, 'NJ'
UNION ALL
SELECT 2, 'NY'
UNION ALL
SELECT 3, 'CT'
;
WIth CTE(userID,userFirstName,UserInitial,UserLastName,UserType,userEmail,Val) AS
(
SELECT t.userID,t.userFirstName,t.UserInitial,t.UserLastName,t.UserType,t.userEmail,b.Val FROm @user_profiles t
CROSS APPLY ParseValues(t.userOfficeIDs)b
)


SELECT DISTINCT r.userID,userFirstName,UserInitial,UserLastName,userEmail,userTypeDesc,left(ol.officelist,len(ol.officelist)-1)
FROM 
(SELECT DISTINCT userID,userFirstName,UserInitial,UserLastName,UserType,userEmail,userTypeDesc,Val FROM CTE m
INNER JOIN @user_types ut
ON ut.userTypeID=m.UserType)r
CROSS APPLY (SELECT ol.officeState + ',' AS [text()]
FROM CTE m1
INNER JOIN @office_locations ol
ON ol.officeID=m1.Val
WHERE m1.userID=r.userID
FOR XML PATH(''))ol(officelist)



output
userID      userFirstName        UserInitial UserLastName         userEmail            userTypeDesc         OfficeLocations
----------- -------------------- ----------- -------------------- -------------------- -------------------- --------------------
1           John                 J           Smith                emailAddress         Manager              NJ
2           Mary                 J           Smith                emailAddress         Director             NJ,NY
3           Jack                 J           Smith                emailAddress         Director             NJ,CT

quote:

---

Originally posted by visakh16

Also it would be much easier if you could use a seperate table to store the office ids rather then storing it as comma seperated list inside user_profiles table istself.

---

quote:

---

Originally posted by bielen

quote:

---

Originally posted by visakh16

Also it would be much easier if you could use a seperate table to store the office ids rather then storing it as comma seperated list inside user_profiles table istself.

---

viskah16:

Thank you for your detailed reply. As per your advice and further research on the web, I have created a separate table to store the officeID.

I've updated the query as follows:

SELECT user_profiles.userFirstName, user_profiles.userInitial, user_profiles.userLastName,
user_types.userTypeDesc, user_profiles.userEmail, user_location_index.officeID
FROM user_profiles INNER JOIN
user_types ON user_types.userTypeID = user_profiles.userType INNER JOIN
user_location_index ON user_profiles.userID IN (user_location_index.userID)
ORDER BY user_profiles.userLastName

How do I now link in the office state location to actually display the respective state/states. I tried an INNER JOIN on officeID to officeLocations, but didn't achieve the desired results. It displayed all states for each entry.

user_profiles
-------------
userID, userFirstName, userInitial, UserLastName, UserType, userEmail
1 John, J, Smith, 1, emailAddress
2 Mary, J, Smith, 2, emailAddress
3 Jack, J, Smith, 2, emailAddress

user_types
----------
userTypeID, userTypeDesc
1 Manager
2 Director
3 VP

office_locations
----------
officeID, officeState
1 NJ
2 NY
3 CT

user_location_index
ID,userID,officeID
1,1,1
2,2,1
3,2,2
4,3,1
5,3,3

Thanks again for your help. The learning experience is appreciated.

---

SELECT user_profiles.userID, 
user_profiles.userFirstName, 
user_profiles.userLastName, 
user_types.userTypeDesc,
LEFT(ll.LocationList,LEN(ll.LocationList)-1) AS Locations  
FROM user_profiles 
INNER JOIN user_types 
ON user_profiles.userType = user_types.userTypeID 
INNER JOIN user_location_index 
ON user_profiles.userID = user_location_index.userID 
CROSS APPLY(
SELECT locationDesc + ',' AS [text()]
FROM user_locations
WHERE locationID= user_location_index.locationID
FOR XML PATH(''))ll(LocationList) 

quote:

---

Originally posted by visakh16

Try like this:-

SELECT user_profiles.userID, 
user_profiles.userFirstName, 
user_profiles.userLastName, 
user_types.userTypeDesc,
LEFT(ll.LocationList,LEN(ll.LocationList)-1) AS Locations  
FROM user_profiles 
INNER JOIN user_types 
ON user_profiles.userType = user_types.userTypeID 
INNER JOIN user_location_index 
ON user_profiles.userID = user_location_index.userID 
CROSS APPLY(
SELECT locationDesc + ',' AS [text()]
FROM user_locations
WHERE locationID= user_location_index.locationID
FOR XML PATH(''))ll(LocationList) 

---

SELECT user_profiles.userID, 
user_profiles.userFirstName, 
user_profiles.userLastName, 
user_types.userTypeDesc,
LEFT(ll.LocationList,LEN(ll.LocationList)-1) AS Locations  
FROM user_profiles 
INNER JOIN user_types 
ON user_profiles.userType = user_types.userTypeID  
CROSS APPLY(
SELECT ul.locationDesc + ',' AS [text()]
FROM user_locations ul
INNER JOIN user_location_index uli
ON ul.locationID= uli.locationID
WHERE uli.userID =user_profiles.userID 
FOR XML PATH(''))ll(LocationList)

quote:

---

Originally posted by visakh16

i think it should be like this seeing your table DDL

SELECT user_profiles.userID, 
user_profiles.userFirstName, 
user_profiles.userLastName, 
user_types.userTypeDesc,
LEFT(ll.LocationList,LEN(ll.LocationList)-1) AS Locations  
FROM user_profiles 
INNER JOIN user_types 
ON user_profiles.userType = user_types.userTypeID  
CROSS APPLY(
SELECT ul.locationDesc + ',' AS [text()]
FROM user_locations ul
INNER JOIN user_location_index uli
ON ul.locationID= uli.locationID
WHERE uli.userID =user_profiles.userID 
FOR XML PATH(''))ll(LocationList)

---

quote:

---

Originally posted by bielen

visakh16:

Thank you. I now have it working exactly the way I want. I'm going to research the use of the CROSS APPLY with XML in SQL 2005. This was the first time I had a need to use the functionality.

Thanks

quote:

---

Originally posted by visakh16

i think it should be like this seeing your table DDL

SELECT user_profiles.userID, 
user_profiles.userFirstName, 
user_profiles.userLastName, 
user_types.userTypeDesc,
LEFT(ll.LocationList,LEN(ll.LocationList)-1) AS Locations  
FROM user_profiles 
INNER JOIN user_types 
ON user_profiles.userType = user_types.userTypeID  
CROSS APPLY(
SELECT ul.locationDesc + ',' AS [text()]
FROM user_locations ul
INNER JOIN user_location_index uli
ON ul.locationID= uli.locationID
WHERE uli.userID =user_profiles.userID 
FOR XML PATH(''))ll(LocationList)

---

---