SQL - Verknüpfung mehrerer Tabellen und Auswahl des ersten Datensatzes nach Bedingungen

Question

Ich habe die folgenden Tabellen:

Projects (ID, Name, ManagerUser_ID)
Users(ID, Name, Active)
Delegates(ProjectID, UserID, OrderNo)

El ManagerUser_ID ist der Projektleiter für das Projekt und stellt eine Verbindung zur Tabelle der Benutzer dar. Benutzer können jedoch sein Inactive . Die Tabelle "Delegates" ist also eine "Many-to-Many"-Tabelle, in der die Benutzer definiert sind, die auf die Projektdaten zugreifen können.

Was ich benötige, ist die Auswahl des ersten Delegierten für ein Projekt, das Active vorausgesetzt, der Manager ist Inactive . El OrderNo gibt die Reihenfolge der Delegierten an (1 ist die erste).

Einige Beispieldaten:

Project
1, Project1, 2
2, Project2, 4
3, Project3, 1

Users
1, Joe, true
2, John, false
3, Dave, true
4, Bob, false

Delegates
1, 4, 1
1, 1, 2
1, 3, 3
2, 2, 1
2, 4, 2
2, 3, 3

Die Ausgabe meiner Abfrage müsste also zeigen:

Project1, Joe
Project2, Dave
Project3, Joe

Projekte und Benutzer für jedes Projekt anzeigen, wenn die ManagerUser_ID inaktiv ist, dann wählen Sie die Option User en Delegates mit den niedrigsten OrderNo das ist Active .

Answer 1

Ich denke, es wird in etwa so aussehen. Die Idee ist, eine zusätzliche Verknüpfung (mit Left Join) durchzuführen, um eine frühere gültige Zeile zu finden. Wenn Sie diese Zeile finden können, dann ist die aktuelle Zeile, die Sie erstellen, offensichtlich nicht die gewünschte:

select p.Name,m.Name from Projects p inner join Users m on p.ManagerUser_ID = m.ID and m.Active = 1
union all
select
    p.Name,
    u.Name
from
    Projects p
        inner join
    Delegates d
        on
            p.ID = d.ProjectID
        inner join
    Users u
        on
            d.UserID = u.ID and
            u.Active = 1
        left join
    Delegates d_anti
        inner join
    Users u_anti
        on
            d_anti.UserID = u_anti.ID and
            u_anti.Active = 1
        on
            p.ID = d_anti.ProjectID and
            d_anti.OrderNo < d.OrderNo
where
    u_anti.ID is null

Answer 2

Select projectName, userName
From

(
    Select projectName, userName, row_number() over (partition by projectName order by priority ASC) as rank
    From
    (
        SELECT p.name as projectName, u.name as userName, 1 as priority
        FROM projects p INNER JOIN users u ON u.active = true and u.id = p.ManagerUser_ID

        UNION

        SELECT TOP(1) p.name, u.name, 2
        FROM Delegates d INNER JOIN projects p  ON p.id = d.projectId
                         INNER JOIN users u ON u.id = d.userId
        Where u.active = true
        Order by u.OrderNo ASC
    )
)
where rank = 1

Answer 3

Dies scheint zu funktionieren (unter Verwendung der Fensterfunktion)

SELECT  P.Name, ISNULL(U.Name, FirstDelegate.Name) AS ProjManager
FROM    Projects P LEFT OUTER JOIN
        Users U ON P.ManagerUser_ID = U.ID AND U.Active = 1 LEFT OUTER JOIN
        (
            SELECT * FROM
            (
                SELECT D.ProjectID, 
                       US.Name, 
                       ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY OrderNo) AS SeqNo
                FROM  Delegates D INNER JOIN
                      Users US ON D.UserID = US.ID
                WHERE US.Active = 1
            ) AS Del
            WHERE Del.SeqNo = 1
        ) AS FirstDelegate ON P.ID = FirstDelegate.ProjectID

Answer 4

select p.name, 
u.name, 
min(d.orderNo)
from projects p, 
users u, 
users manager, 
delegates d
where p.ManagerUser_ID = manager.id
and manager.active = false
and p.id = d.projectId
and d.userid = u.id
group by p.name, 
u.name