Link to home
Start Free TrialLog in
Avatar of TECH_NET
TECH_NET

asked on

Help with SQL Procedure Query

I have a table with few columns and my questions related to the 2 columns only.
let me say
STUDENT_ID,AUDIENCE_ID,CLASS_ID
-1,2,10
-1,2,10
1,1,10
2,1,10
3,1,10

As you see the table stores the student_id as -1 when the audience_id is 2

I have the following query

SELECT * FROM <TableNAME>
WHERE
(CLASS_ID IN (SELECT CLASS_ID FROM STUDENT_MANAGEMENT_REGISTRATION) ) AND      
(STUDENT_ID=@STUDENT_ID OR @STUDENT_ID=-1   )

Now what i need to do is built a query that retrieve records based on the condition clause.

i wish to retrieve records pertaining to the student_id passed in the parameter

Say , the value is -1 then i am getting the relevant results.
Now, the value of @STUDENT_ID=1,then i am getting the relevant results but only partial
let me explain further. When @STUDENT_ID=1 , i would need all the results where there is a match for student_id=1 aswells as the records
that pertain to the class_id =10 where audience_id=2.

Finally if i pass @STUDENT_ID=2, which is not found the illustration of records above, the records should be retrieved the top 2 records in the
illustration needs to be retrieved if the audience_id=2 and clas_id=10

In short how can i change the where clause conditions to ensure i get the results.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TECH_NET
TECH_NET

ASKER

In the above illustration, the records retrieved for student_id=1 would be
-1,2,10
-1,2,10
1,1,10

Since the first 2 rows match the criteria since the audience_id=2 and class_id=10

The reason why this occurs is the input form has 2 options, sent the homework to entire class(value is 2) and sent the homework to a specific student.

Hence where ever the student_id=-1 , it means that the homework has been sent to the entire class and not a specific student.
currently i got is partially working but not sure all scenerios have been taken care

SELECT
      DISTINCT *
FROM
      ACADEMICS_HOMEWORK_MANAGEMENT_VIEW
WHERE
                   (CLASS_ID=@CLASS_ID OR @CLASS_ID=01) AND
      (STUDENT_ID=@STUDENT_ID OR STUDENT_ID=-1   ) AND
      (SUBJECT_ID=@SUBJECT_ID OR @SUBJECT_ID=-1   )
SELECT
      DISTINCT *
FROM
      ACADEMICS_HOMEWORK_MANAGEMENT_VIEW
WHERE
                   (CLASS_ID=@CLASS_ID OR @CLASS_ID=-1) AND
      (STUDENT_ID=@STUDENT_ID OR STUDENT_ID=-1   ) AND
      (SUBJECT_ID=@SUBJECT_ID OR @SUBJECT_ID=-1   )

You did not show subjectid in your table, change it to


SELECT
      DISTINCT *
FROM
      ACADEMICS_HOMEWORK_MANAGEMENT_VIEW
WHERE  (CLASS_ID=@CLASS_ID OR @CLASS_ID=-1)
AND (STUDENT_ID=@STUDENT_ID OR STUDENT_ID=-1)
AND (SUBJECT_ID=@SUBJECT_ID OR SUBJECT_ID=-1)  
ewangoya: i do not see any changes to the query in the posting you made. It is exactly similar to the one i posted.
Look. At the last line onsubject id
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Is it like on Parameter studentid You need to find its class and then from that class display Studentid -1 and passing studentid