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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
75 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  
 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.07 seconds. Powered By: Snitz Forums 2000