Une idée sur la façon de résoudre ce problème de dépendance de requête? La sous-requête pourrait-elle aider? La database que j'utilise est sql server 2012.
FROM [Scheduling].[studentsection] AS [table027] Left JOIN [Grading].[StudentGradeBucket] AS [table028] ON ([table028].[StudentSectionID] = [table027].[StudentSectionID]) And (@0 = [table002].[label]) Left JOIN [Grading].[GradingPeriodGradeBucket] AS [table029] ON [table028].[GradingPeriodGradeBucketID] = [table029].[GradingPeriodGradeBucketID] Left JOIN [Grading].[GradeBucket] AS [table002] ON [table029].[GradeBucketID] = [table002].[GradeBucketID] Left JOIN [Grading].[GradeBucketType] AS [table001] ON [table002].[GradeBucketTypeID] = [table001].[GradeBucketTypeID] Left JOIN [Grading].[GradeMark] AS [table022] ON [table028].[GradeMarkID] = [table022].[GradeMarkID]
Le problème de dépendance que j'ai est avec ceci:
@0 = [table002].[label] //@0 is a ssortingng variable
Comme la jointure n'a pas encore été créée mais je dois l'utiliser pour créer la jointure pour la relation [Grading]. [StudentGradeBucket] ou [table028]
C'est une étrange logique de jointure circulaire que vous utilisez et ces alias de table que vous avez choisis le rendent plus confus. Alias devrait simplifier, ne pas confondre. Cela dit, je pense que cela peut juste être déplacé à la clause where:
... Left JOIN [Grading].[GradeMark] AS [table022] ON [table028].[GradeMarkID] = [table022].[GradeMarkID] where @0 = [table002].[label]
Si cela échoue, vous devrez peut-être redéfinir votre logique … cela me semble un peu circulaire.
Essaye ça:
declare @0 nvarchar(max) = 'label value' select * from [Scheduling].[studentsection] as ss left join [Grading].[StudentGradeBucket] as sgb on sgb.[StudentSectionID] = ss.[StudentSectionID] inner join [Grading].[GradingPeriodGradeBucket] as gpgb on gpgb.[GradingPeriodGradeBucketID] = sgb.[GradingPeriodGradeBucketID] inner join [Grading].[GradeBucket] as gb on gb.[GradeBucketID] = gpgb.[GradeBucketID] and gb.[label] = @0 left join [Grading].[GradeBucketType] as gbt on gbt.[GradeBucketTypeID] = gb.[GradeBucketTypeID] left join [Grading].[GradeMark] as gm on gm.[GradeMarkID] = sgb.[GradeMarkID]
Ou si vous voulez vraiment des jointures externes gauche:
declare @0 nvarchar(max) = 'label value' select * from [Scheduling].[studentsection] as ss left join [Grading].[StudentGradeBucket] as sgb on sgb.[StudentSectionID] = ss.[StudentSectionID] left join [Grading].[GradingPeriodGradeBucket] as gpgb on gpgb.[GradingPeriodGradeBucketID] = sgb.[GradingPeriodGradeBucketID] left join [Grading].[GradeBucket] as gb on gb.[GradeBucketID] = gpgb.[GradeBucketID] and gb.[label] = @0 left join [Grading].[GradeBucketType] as gbt on gbt.[GradeBucketTypeID] = gb.[GradeBucketTypeID] left join [Grading].[GradeMark] as gm on gm.[GradeMarkID] = sgb.[GradeMarkID]
Ou si vous avez besoin de la logique consistant à ne renvoyer que les résultats de StudentGradeBucket lorsqu'il existe un logging GradeBucket correspondant; ce:
declare @0 nvarchar(max) = 'label value' select * from [Scheduling].[studentsection] as ss left join [Grading].[StudentGradeBucket] as sgb on sgb.[StudentSectionID] = ss.[StudentSectionID] left join [Grading].[GradingPeriodGradeBucket] as gpgb on gpgb.[GradingPeriodGradeBucketID] = sgb.[GradingPeriodGradeBucketID] left join [Grading].[GradeBucket] as gb on gb.[GradeBucketID] = gpgb.[GradeBucketID] left join [Grading].[GradeBucketType] as gbt on gbt.[GradeBucketTypeID] = gb.[GradeBucketTypeID] left join [Grading].[GradeMark] as gm on gm.[GradeMarkID] = sgb.[GradeMarkID] where ( --filter on the gb label only if there's a result from the sgb table; sgb.[StudentSectionID] is null or and gb.[label] = @0 )