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