vendredi 22 mai 2015

Associate couples between 2 tables with SQL Server 2005

The question is easy, answer is not (for me).

I have 2 identical tables composed of 2 columns : ID and value. I have to find all couples sharing the same value. So when a record is used in a couple, it can't be reused in another couple.

For example, with this two tables :

CREATE TABLE [Tab1]([ID1] [int], [Val] [int]) 
CREATE TABLE [Tab2]([ID2] [int], [Val] [int])
INSERT [Tab1] ([ID1], [Val]) VALUES (1, 10)
INSERT [Tab1] ([ID1], [Val]) VALUES (2, 20)
INSERT [Tab1] ([ID1], [Val]) VALUES (3, 20)
INSERT [Tab1] ([ID1], [Val]) VALUES (4, 50)
INSERT [Tab1] ([ID1], [Val]) VALUES (5, 100)
INSERT [Tab2] ([ID2], [Val]) VALUES (1, 20)
INSERT [Tab2] ([ID2], [Val]) VALUES (2, 10)
INSERT [Tab2] ([ID2], [Val]) VALUES (3, 50)
INSERT [Tab2] ([ID2], [Val]) VALUES (4, 30)
INSERT [Tab2] ([ID2], [Val]) VALUES (5, 20)
GO

a good answer would be (there are several solutions, but one is enough) :

ID1   ID2
---  ----
2    1
1    2
4    3
3    5

I'm looking for a query to find this result. I use SQL Server 2005 but I can use SQL Server 2008 if it's needed.

Aucun commentaire:

Enregistrer un commentaire