Eine etwas komplexere Sortierung

[marabu]

Ich habe mal zwei Tabellen erzeugt:

SQL-Code:

CREATE TABLE [dbo].[Vertraege] (
   [ID] [int] IDENTITY (1, 1) NOT NULL ,
   [DATUM] [datetime] NOT NULL ,
   [P1] [int] NULL ,
   [P2] [int] NULL ,
   [P3] [int] NULL 
)

Code:

// Vertraege
1    2006-05-17 00:00:00.000    1    NULL    NULL
2    2006-05-17 00:00:00.000    2    3       NULL

SQL-Code:

CREATE TABLE [dbo].[Personen] (
   [ID] [int] IDENTITY (1, 1) NOT NULL ,
   [NAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL 
)

Code:

// Personen
1   Müller
2   Meier
3   Schulze

Hier die Abfrage:

SQL-Code:

select name, v.* 
from vertraege v
join personen p on v.p1 = p.id
UNION
select name, v.* 
from vertraege v
join personen p on v.p2 = p.id
UNION
select name, v.* 
from vertraege v
join personen p on v.p3 = p.id

Geliefert wird dann:

Code:

Meier     2   2006-05-17 00:00:00.000   2   3      NULL
Müller    1   2006-05-17 00:00:00.000   1   NULL   NULL
Schulze   2   2006-05-17 00:00:00.000   2   3      NULL

marabu