CODEWITHSUNDEEP
sqlsql-servert-sql
RubbelDieKatz
RubbelDieKatz

Reputation: 1187

SQL Server: Getting opposite entry to a row

This question is a bit hard to explain.

Every appraisal has an opposite appraisal. An employee appraises their educator and the educator appraises the employee. Thus, we have 2 appraisal rows for each plan element, which contains the timeframe.

I want to create an SQL statement which returns appraisals which are opposite to the one that is given to the statement as a parameter.

This is how far I've gotten and I'm stuck. The statement I've made doesn't even make sense.

DECLARE @Appraisal_ID_Param INTEGER
SET @Appraisal_ID_Param = ?

SELECT *
FROM [Appraisal]
RIGHT JOIN [Plan]
ON 
    [Plan].[Plan_ID] = [Appraisal].[Plan_ID] AND
    [Appraisal].[Appraisal_ID] != @Appraisal_ID_Param;

Here's a part of my database model:

database model

Basically, I need to return those appraisals that are not @Appraisal_ID_Param but share the same Plan_ID.

I really hope that I was clear enough because I've been stuck on this issue for quite a while and it's rather confusing.

Upvotes: 1

Views: 111

Answers (3)

Rigerta
Rigerta

Reputation: 4039

Apparently one appraisal has one plan on your database, which means you could initially find the ID of that plan, based on the AppraisalId you have as a parameter and then select only those appraisals that have the same PlanId but AppraisalID different from the parameter:

DECLARE @Appraisal_ID_Param INTEGER
SET @Appraisal_ID_Param = ?

DECLARE @PlanId INTEGER = (SELECT [Plan_ID] FROM Appraisal WHERE [Appraisal_ID] = @Appraisal_ID_Param)

SELECT * -- all information
FROM 
          [Appraisal] appr 
     JOIN [Plan] p ON  p.[Plan_ID] = appr.[Plan_ID]
WHERE 
          p.[Plan_ID] = @PlanId 
      AND appr.[Appraisal_ID] <> @Appraisal_ID_Param

Upvotes: 2

nico boey
nico boey

Reputation: 389

something like this?

DECLARE @Appraisal_ID_Param INTEGER
SET @Appraisal_ID_Param = ?

SELECT top 1 *
FROM Appraisal 
where 
    plan_id = (select plan_id 
               from Appraisal 
               where Appraisal_ID = @Appraisal_ID_Param
               ) 
    and Appraisal_ID <> @Appraisal_ID_Param

Upvotes: 1

dani herrera
dani herrera

Reputation: 51655

You must join Appraisal for twice:

DECLARE @Appraisal_ID_Param INTEGER
SET @Appraisal_ID_Param = ?

SELECT DISTINCT A_TO.*
FROM [Appraisal] A_FROM
RIGHT JOIN [Appraisal] A_TO
ON 
    [A_FROM].[Plan_ID] = [A_TO].[Plan_ID] AND
    [A_TO].[Appraisal_ID] != @Appraisal_ID_Param
WHERE
    [A_FROM].[Appraisal_ID] = @Appraisal_ID_Param

Upvotes: 2

Related Questions