Joining Columns if an Identity Exists

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

Transact-SQL (2008)

Joining Columns if an Identity Exists

Author

Topic

Insider
Starting Member

5 Posts

Posted - 2014-04-27 : 08:46:33

Hi all,

I need help with a query. I have the following sample table A:

Year Round Team A Team B Winner Loser
2000 1 A H H A
2000 1 B R B R
2000 1 C T C T
2000 2 B A B A
2000 2 S C C S
2000 2 H F F H

I also have the following sample table B

Year Round Team A Team B Winner Loser
2000 2 B A B A
2000 2 S C C S
2000 2 H F F H
2000 3 S A R S
2000 3 H B H B

I would like to join Table A with Table B on the condition that round 1 (Table A) matches up with round 2 (Table B), round 2 with round 3 etc. I would like the join to be based not only on the Year and (B.Round - 1) but also on Team A and Team B. So for example, in round 2 we can see the first game was Team B vs Team A. I want to align this game with round 1 where Team A played Team H. This is easily possibly by creating some join on Team A. However this is where I really need the SQL guru's help, the second game of round 2 sees Team S play Team C. As per my logic thus far, I would like to align this with game 3 of round 1 where Team C plays Team T but because Team S didn't play in the previous round (i.e round 1) I want to exclude this join! So basically, if Team A plays Team B and either Team A or Team B hasn't played in the previous round, do not include them in the join.

Here is the table I strive to achieve (Table A + 3 columns (Round, Team A, Team B) from Table B):

Year Round Team A Team B Winner Loser Round Team A Team B
2000 1 A H H A 2 B A
2000 1 B R B R 2 B A
2000 2 B A B A 3 S A
2000 2 S C C S 3 S A
2000 2 H F F H 3 H B

In summary just to clarify, I want to join rounds with the previous round provided that both teams playing in the current round played /existed in the previous round also (i.e. there was no BYE).

Thanks in advance. Fingers cross someone has a solution.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-27 : 20:59:40

like following?

SELECT
	A.*,
	B.[Round],
	B.TeamA,
	B.TeamB
FROM A
INNER JOIN B
	ON A.[Round] + 1 = B.[Round]
	AND (
		A.TeamA IN (B.TeamA, B.TeamB)
		OR A.TeamB IN (B.TeamA, B.TeamB))
WHERE EXISTS(
	SELECT *
	FROM A
	WHERE A.[Round] + 1 = B.[Round]
	AND B.TeamA IN (A.TeamA, A.TeamB))
AND EXISTS(
	SELECT *
	FROM A
	WHERE A.[Round] + 1 = B.[Round]
	AND B.TeamB IN (A.TeamA, A.TeamB))

-------------------------------------
From Japan
Sorry, my English ability is limited.

Insider
Starting Member

5 Posts

Posted - 2014-04-27 : 21:10:22

quote:

Originally posted by nagino

like following?

SELECT
	A.*,
	B.[Round],
	B.TeamA,
	B.TeamB
FROM A
INNER JOIN B
	ON A.[Round] + 1 = B.[Round]
	AND (
		A.TeamA IN (B.TeamA, B.TeamB)
		OR A.TeamB IN (B.TeamA, B.TeamB))
WHERE EXISTS(
	SELECT *
	FROM A
	WHERE A.[Round] + 1 = B.[Round]
	AND B.TeamA IN (A.TeamA, A.TeamB))
AND EXISTS(
	SELECT *
	FROM A
	WHERE A.[Round] + 1 = B.[Round]
	AND B.TeamB IN (A.TeamA, A.TeamB))

-------------------------------------
From Japan
Sorry, my English ability is limited.

Thankyou I will try it.

Insider
Starting Member

5 Posts

Posted - 2014-04-28 : 08:21:46

quote:

Originally posted by nagino

like following?

SELECT
	A.*,
	B.[Round],
	B.TeamA,
	B.TeamB
FROM A
INNER JOIN B
	ON A.[Round] + 1 = B.[Round]
	AND (
		A.TeamA IN (B.TeamA, B.TeamB)
		OR A.TeamB IN (B.TeamA, B.TeamB))
WHERE EXISTS(
	SELECT *
	FROM A
	WHERE A.[Round] + 1 = B.[Round]
	AND B.TeamA IN (A.TeamA, A.TeamB))
AND EXISTS(
	SELECT *
	FROM A
	WHERE A.[Round] + 1 = B.[Round]
	AND B.TeamB IN (A.TeamA, A.TeamB))

-------------------------------------
From Japan
Sorry, my English ability is limited.

Fantastic, you gave me the ideas necessary to complete what I needed to do. Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources