How to create a fixture where every team will play once with the other teams in SQL-CodePudding

Input::

Team
T1
T2
T3
T4

Output::

Team  Opponent
T1    T2
T1    T3
T1    T4
T2    T3
T2    T4
T3    T4

Please help us providing the Oracle SQL statement without using PL/SQL block.

Thank you...

CodePudding user response：

You can use a hierarchical query (which avoids the need for a self-join):

SELECT PRIOR team AS team,
       team AS opponent
FROM   table_name
WHERE  LEVEL = 2
CONNECT BY PRIOR team < team;

Which, for the sample data:

CREATE TABLE table_name (team) AS
SELECT 'T' || LEVEL FROM DUAL CONNECT BY LEVEL <= 4;

Outputs:

TEAM OPPONENT

T1 T2

T1 T3

T1 T4

T2 T3

T2 T4

T3 T4

TEAM	OPPONENT
T1	T2
T1	T3
T1	T4
T2	T3
T2	T4
T3	T4

db<>fiddle here

CodePudding user response：

That's self join.

Sample data:

SQL> with test (team) as
  2    (select 'T1' from dual union all
  3     select 'T2' from dual union all
  4     select 'T3' from dual union all
  5     select 'T4' from dual
  6    )

Query:

  7  select a.team, b.team opponent
  8  from test a join test b on b.team > a.team
  9  order by 1, 2
 10  /

TEAM       OPPONENT
---------- ----------
T1         T2
T1         T3
T1         T4
T2         T3
T2         T4
T3         T4

6 rows selected.

SQL>