SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining Columns if an Identity Exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Insider
Starting Member

5 Posts

Posted - 04/27/2014 :  08:46:33  Show Profile  Reply with Quote
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.

Edited by - Insider on 04/27/2014 08:50:04

nagino
Yak Posting Veteran

Japan
68 Posts

Posted - 04/27/2014 :  20:59:40  Show Profile  Reply with Quote
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.
Go to Top of Page

Insider
Starting Member

5 Posts

Posted - 04/27/2014 :  21:10:22  Show Profile  Reply with Quote
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.
Go to Top of Page

Insider
Starting Member

5 Posts

Posted - 04/28/2014 :  08:21:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000