Displaying Records associated with Records...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Displaying Records associated with Records...

Author

Topic

buffer
Starting Member

6 Posts

Posted - 2007-06-21 : 21:19:04

I cannot figure out how to write a sql statement to display the multiple records of multiple records. I know that doesn't make much sense so let me explain:

This problem involves two tables. They both reference (FK) data in a third table (tblDepartment).

Here are the fields and some test data in each of the two important tables:


=============
tblDepartment =============================
departmentID	userID
-----------------------------
1		user1
2		user1
1		user2
2		user3
3		user3
3		user4


===========
tblRequest  ========================================
requestID	departmentID	userID
----------------------------------------
1		1		user1
2		1		user2
3		2		user3
4		3		user3
5		3		user4
6		2		user1

What I am trying to do is display all requests associated with the multiple departments of "user1". In the above example, I would want to see (after a select statement):


requestID	departmentID	userID
----------------------------------------
1		1		user1
2		1		user2
3		2		user3
5		2		user1

We don't see the requests of user3 or user4 when the given request's departmentID is not a department associated with user1.

Hopefully someone out there understands this. I've never had to write a select statement like this before, so I'm having trouble wrapping my mind around it. Thanks for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 21:26:28

[code]DECLARE @tblDepartment TABLE
(
departmentID int,
userID varchar(5)
)
INSERT INTO @tblDepartment
SELECT 1, 'user1' UNION ALL
SELECT 2, 'user1' UNION ALL
SELECT 1, 'user2' UNION ALL
SELECT 2, 'user3' UNION ALL
SELECT 3, 'user3' UNION ALL
SELECT 3, 'user4'

DECLARE @tblRequest TABLE
(
requestID int,
departmentID int,
userID varchar(5)
)
INSERT INTO @tblRequest
SELECT 1, 1, 'user1' UNION ALL
SELECT 2, 1, 'user2' UNION ALL
SELECT 3, 2, 'user3' UNION ALL
SELECT 4, 3, 'user3' UNION ALL
SELECT 5, 3, 'user4' UNION ALL
SELECT 6, 2, 'user1'

SELECT r.*
FROM @tblDepartment d INNER JOIN @tblRequest r
ON d.departmentID = r.departmentID
WHERE d.userID = 'user1'[/code]

KH
[spoiler]Time is always against us[/spoiler]

buffer
Starting Member

6 Posts

Posted - 2007-06-21 : 23:15:45

Thanks Khtan for the reply!

Is the solution you provided dynamic? Could I just as easily use "where d.userID = 'user2'" without altering any of those unions?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 23:19:28

You don't need the UNION part. That is just for creating sample data for testing the query.

You only required this query

DECLARE @userID	varchar(10)

SELECT	@userID	= 'user1'

SELECT	r.requestID, r.departmentID, r.userID
FROM	tblDepartment d INNER JOIN tblRequest r
	ON	d.departmentID	= r.departmentID
WHERE	d.userID	= @userID

KH
[spoiler]Time is always against us[/spoiler]

buffer
Starting Member

6 Posts

Posted - 2007-06-22 : 10:25:03

Thanks again Khtan. I tried it out this morning and it works perfectly! You are a beautiful, beautiful person.

-buffer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources