Can I do this with a JOIN... or help on...

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

SQL Server 2005 Forums

Transact-SQL (2005)

Can I do this with a JOIN... or help on...

Author

Topic

sepgs2004
Starting Member

2 Posts

Posted - 2009-09-23 : 11:20:27

Following is my situation -
I have two tables:Team and Test
Team (team_id, member_id, name)
Test (test_id, member_id, iteration, details)

Concept
Members are grouped into teams.
Each member will take a test several iterations.

Other could-be assumptions that can be made
Although it does not matter, we can say a member belong to only one team.
For sanity, if we want, we can say no two members will have the same member number even across teams. This is just to keep our heads out of a confusion.

With a SQL JOIN or some other method possibly, given a team_id, I would like to know which members did not take which test by iteration.

Example to illustrate what I would like to have:
TEAM(team_id, member_id): Team 1 has 4 members
(1, 1)
(1, 2)
(1, 3)
(1, 4)

TEST(test_id, member_id, iteration, details): Here are their test/iterations...
(1, 1, 1, etc)
(1, 1, 2, etc)
(1, 1, 3, etc)
(1, 2, 1, etc)
(1, 2, 2, etc)
(1, 3, 1, etc)
(1, 3, 2, etc)
(1, 3, 3, etc)

For the above data, we will assume member 2 forgot to take 3rd iteration of test 1.
Similarly, member 4 got into team 1 recently, so (s)he did not take any iteration on test 1.

Now, given the team_id 1, I would like to get a result like this , so that I can just do add or notify them:
TEST(test_id, member_id, iteration)
(1, 2, 3)
(1, 4, 1)
(1, 4, 2)
(1, 4, 3)

This looks like a standard problem... I am thinking on it by making a join TEAM with TEST....

Good wishes

ddamico
Yak Posting Veteran

76 Posts

Posted - 2009-09-23 : 13:44:29

DECLARE @Team TABLE (
[team_id] SMALLINT,
[member_id] SMALLINT,
[name] CHAR(2)
)

INSERT INTO @Team
SELECT 1, 1, 'AB' UNION
SELECT 1, 2, 'BC' UNION
SELECT 1, 3, 'CD' UNION
SELECT 1, 4, 'DE'

DECLARE @Test TABLE (
[test_id] SMALLINT,
[member_id] SMALLINT,
[iteration] SMALLINT,
[details] CHAR(3)
)

INSERT INTO @Test
SELECT 1, 1, 1, 'etc' UNION
SELECT 1, 1, 2, 'etc' UNION
SELECT 1, 1, 3, 'etc' UNION
SELECT 1, 2, 1, 'etc' UNION
SELECT 1, 2, 2, 'etc' UNION
SELECT 1, 3, 1, 'etc' UNION
SELECT 1, 3, 2, 'etc' UNION
SELECT 1, 3, 3, 'etc'

SELECT W.[test_id]
, W.[member_id]
, W.[iteration]
FROM
(
SELECT DISTINCT
B.[test_id]
, B.[iteration]
, A.[member_id]
FROM @Team A
, @Test B ) W
LEFT OUTER JOIN @Test X
ON W.[test_id] = X.[test_id]
AND W.[iteration] = X.[iteration]
AND W.[member_id] = X.[member_id]
WHERE X.[member_id] IS NULL
ORDER BY 1,2,3

sepgs2004
Starting Member

2 Posts

Posted - 2009-09-23 : 14:53:22

Thank you so much.. I did not mention one thing. I needed to restrict the cartesian product to restrict to team members only...

SELECT W.test_id, W.member_id, W.iteration
FROM
(
SELECT DISTINCT B.test_id, B.iteration, A.member_id
FROM team A, test B
WHERE (b.member_id in (select member_id from team where team_id = a.team_id))
) W
LEFT OUTER JOIN test X ON W.test_id = X.test_id AND W.iteration = X.iteration AND W.member_id = X.member_id
WHERE X.member_id IS NULL
ORDER BY 1,2,3

Good wishes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources