Find existing team_id for combination of player_id

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 2000 Forums

Transact-SQL (2000)

Find existing team_id for combination of player_id

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-29 : 00:04:25

Hi all,
Im having some trouble getting the following query to work. I have tables: PLAYER, TEAM, and PLAYER_TEAM_XREF . I want to pass in two parameters, @p1 and @p2 which are both player_ids.

First I want the query to check to see if a team has already been created for this combination of player_ids. If so, it will return that team_id. If not, it will insert a new row into the team table and return that team_id.

Players can exist on multiple teams, but only one team can exist for any one combination of players. Thank you all for ANY input on this topic in advance.

Heres my setup:


DECLARE @team TABLE (team_id INT, team_name VARCHAR(15))
DECLARE @player TABLE (player_id INT, player_name VARCHAR(15))
DECLARE @player_team_xref TABLE (player_id INT, team_id INT)

SET NOCOUNT ON
INSERT INTO @team SELECT 1,'Team One' UNION SELECT 2, 'Team Two' UNION SELECT 3, 'Team Three'
INSERT INTO @player SELECT 1, 'Nathan' UNION SELECT 2,'John' UNION SELECT 3,'Joe'
INSERT INTO @player_team_xref SELECT 1,1 UNION SELECT 2,2


DECLARE @p1 INT, 
	@p2 INT,
	@team_id INT,
	@row_count INT


SELECT @p1 = 1, @p2 = 2

SELECT @team_id = team_id
FROM player_team_xref 
WHERE player_id IN (@p1,@p2)
GROUP BY team_id

SELECT @row_count = @@ROWCOUNT
IF @row_count <> 1
BEGIN
	SELECT @team_id = MAX(team_id + 1) FROM @team
	INSERT INTO @team
	SELECT @p1, @team_id UNION SELECT @p2, @team_id
END 

SELECT @team_id AS 'team_id'

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-29 : 00:44:06

This is where I am stuck:


DECLARE @team TABLE (team_id INT, team_name VARCHAR(15))
DECLARE @player TABLE (player_id INT, player_name VARCHAR(15))
DECLARE @player_team_xref TABLE (player_id INT, team_id INT)

SET NOCOUNT ON
INSERT INTO @team SELECT 1,'Team One' UNION SELECT 2, 'Team Two' UNION SELECT 3, 'Team Three'
INSERT INTO @player SELECT 1, 'Nathan' UNION SELECT 2,'John' UNION SELECT 3,'Joe'
INSERT INTO @player_team_xref SELECT 1,1 UNION SELECT 2,1 UNION SELECT 3,2 UNION SELECT 2,2


DECLARE @p1 INT, 
	@p2 INT,
	@team_id INT,
	@row_count INT,
	@cnt INT
SET @cnt = 2


SELECT @p1 = 3, @p2 = 2

SELECT team_id--, SUM(team_id)
FROM @player_team_xref
WHERE player_id IN (@p1,@p2)
GROUP BY team_id
HAVING SUM(team_id)/@cnt = team_id

This query works for a select group of possibilities. But it does not exclude team_id when players exist in a team not passed in as param.

For example, try with this xref insert:

INSERT INTO @player_team_xref SELECT 1,1 UNION SELECT 2,1 UNION SELECT 3,2 UNION SELECT 2,2 UNION SELECT 3,3 UNION SELECT 2,3 UNION SELECT 1,3

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-29 : 10:42:25

Well, this is what Ive come up with so far.

SELECT ptx.team_id
FROM @player_team_xref ptx 
	INNER JOIN (	SELECT 	team_id, 
				COUNT(player_id) AS 'player_cnt' 
			FROM @player_team_xref 
			GROUP BY team_id) d
	ON ptx.team_id = d.team_id
WHERE player_id IN (@p1,@p2) AND d.player_cnt = @cnt
GROUP BY ptx.team_id
HAVING SUM(ptx.team_id)/@cnt = ptx.team_id

SELECT @row_count = @@ROWCOUNT
IF @row_count < 1
BEGIN
	PRINT 'insert new team_id for these players...'
END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 11:23:21

not sure what you are doing with the HAVING SUM(team_id)/@cnt = team_id part.

You just need to make sure that 2 rows match, one for each playerID:

SELECT @p1 = 3, @p2 = 2

SELECT team_id
FROM @player_team_xref
WHERE player_id IN (@p1,@p2)
GROUP BY team_id
HAVING COUNT(*) = 2

That's it! The above select returns a list of team_id's which contain both players.

And of course, this only works reliably if your table @player_team_xref has a primary key or a unique constraint on both columns.

- Jeff

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-29 : 11:34:52

Hi J,
Thanks for the reply. The reason I used that is because player_id can belong to more than one team_id, AND, a team can have more than 2 player_ids. Try the following:

DECLARE @team TABLE (team_id INT, team_name VARCHAR(15))
DECLARE @player TABLE (player_id INT, player_name VARCHAR(15))
DECLARE @player_team_xref TABLE (player_id INT, team_id INT)

SET NOCOUNT ON
INSERT INTO @team SELECT 1,'Team One' UNION SELECT 2, 'Team Two' UNION SELECT 3, 'Team Three'
INSERT INTO @player SELECT 1, 'Nathan' UNION SELECT 2,'John' UNION SELECT 3,'Joe'
INSERT INTO @player_team_xref SELECT 1,1 UNION SELECT 2,1 UNION SELECT 3,2 UNION SELECT 2,2 UNION SELECT 3,3 UNION SELECT 2,3 UNION SELECT 1,3


DECLARE @p1 INT, 
	@p2 INT,
	@team_id INT,
	@row_count INT,
	@cnt INT

SET @cnt = 2


SELECT @p1 = 1, @p2 = 2

SELECT team_id
FROM @player_team_xref
WHERE player_id IN (@p1,@p2)
GROUP BY team_id
HAVING COUNT(*) = 2

This results in (2) team_ids returned because player_id 1 is now part of both team_id=1 and team_id=3. I used that last clause to exclude the team_ids that contain the player_ids we are looking for (1,2), but also contain an additional player_id (1,2,3).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 11:57:22

ah, a requirement you did not specify! very important to be very precise with what you need.

here's one way to ensure that the team only has 2 players:

SELECT team_id
FROM @player_team_xref
GROUP BY team_id
HAVING COUNT(*) = 2 and
sum(case when player_id in (@p1,@p2) then 1 else 0 end) = 2

- Jeff

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-03-29 : 12:00:26

Jeff,
Nice work, as usual.

I tried to specify that requirement in the orig post:
> "Players can exist on multiple teams, but only one team can exist for any one combination of players."
But I guess, upon re-read it is not vey clear.

Thanks for the help!

Nathan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources