SQL Self Join without double cross results [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I'm searching for the solution since hours, but cat get it.

My table:

ID         name          value1      value2
1          Meyer         20          _
2          Schmitt       20          _
3          Berger        _           20
4          Chief         _           20

my query:

SELECT A.ID, A.name AS nameA, B.name AS nameB, A.value1 
FROM table AS A INNER JOIN table AS B
ON A.value1 = B.value2

result:

ID      nameA      nameB       value1
1       Meyer      Berger      20
1       Meyer      Chief       20
2       Schmitt    Berger      20
2       Schmitt    Chief       20  

This should look like:

ID      nameA       nameB      value1
1      开发者_StackOverflow Meyer       Berger     20
2       Schmitt     Chief      20

How can I get this result?

I also tried the GROUP BY A.name, but get the wrong result:

ID       nameA      nameB     value1
1        Meyer      Berger    20
2        Schmitt    Berger    20

---

You have not specified DBMS. This is tested in SQL Server:

with C as
(
  select *,
         row_number() over(partition by value1 order by ID) as rn1,
         row_number() over(partition by value2 order by ID) as rn2
  from YourTable
)
select A.ID, 
       A.name as nameA, 
       B.name as nameB, 
       A.value1  
from C as A
  inner join C as B
    on A.value1 = B.value2 and
       A.rn1 = B.rn2

Testable sample:

declare @T table
(
  ID int,
  name varchar(10),
  value1 int,
  value2 int
)
insert into @T values
(1,          'Meyer',         20,          null),
(2,          'Schmitt',       20,          null),
(3,          'Berger',        null,        20),
(4,          'Chief',         null,        20)

;with C as
(
  select *,
         row_number() over(partition by value1 order by ID) as rn1,
         row_number() over(partition by value2 order by ID) as rn2
  from @T
)
select A.ID, 
       A.name as nameA, 
       B.name as nameB, 
       A.value1  
from C as A
  inner join C as B
    on A.value1 = B.value2 and
       A.rn1 = B.rn2

---

According to this join criteria A.value1 = B.value2 and your input table, Meyer is related to both Berger and Chief, and Schmitt is related to both Berger and Chief. So a suspect there is more to the criteria/business rules of how these are related that you are not telling us. It looks like you are just using value1 and value2 as some funky foreign keys. It is not clear why you think that Meyer wouldn't be related to Chief, or why Schmitt wouldn't be related to Berger.

ID         name          value1      value2
1          Meyer         20          _
2          Schmitt       20          _
3          Berger        _           20
4          Chief         _           20

So it's not a problem with your query, but a problem with your data structure, so let's fix value1 and value2:

ID         name          value1      value2
1          Meyer         10          _
2          Schmitt       20          _
3          Berger        _           10
4          Chief         _           20

Now you will get the correct results. But a more traditional design would look like this:

PersonId   PersonName    BossPersonId
1          Meyer         3
2          Schmitt       4
3          Berger        _
4          Chief         _

With this query:

Select A.Id, A.PersonName, B.PersonName as BossName
FROM table AS A INNER JOIN table AS B
ON A.BossPersonId= B.PersonId   

---

I solved it right now.

TABLE:

ID       name         value1         value2
1        Meyer        20             _
2        Muster       10             _
3        Heinz        20             _
4        Karl         _              20
5        Max          _              20
6        Zack         _              10

Split it in temp tables and order it by value

TABLE A:

ID       name        value1        value2
1        Muster      10            _
2        Meyer       20            _
3        Heinz       20            _

TABLE B:

ID       name        value1        value2
1        Zack        _             10
2        Karl        _             20
3        Max         _             20

and then join on A.ID=B.ID AND A.value1=B.value2

Post with rownumber helped me a lot, thx best regards