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.
TECH_NETAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>Finally if i pass @STUDENT_ID=2, which is not found the illustration of records above
please review the data, it is there :)

anyhow, how do you determine the alternative rows to be retrieved?
I presume I would do this (in a stored procedure) with a temp table,
where after a insert of the "first pass" into the temp table, if it's still empty,
perform a second pass ...

>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.
shall it be class_id=10 because that is the value for class_id for student=1 ?
please clarify



0
 
TECH_NETAuthor Commented:
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.
0
 
TECH_NETAuthor Commented:
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   )
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
TECH_NETAuthor Commented:
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   )
0
 
Ephraim WangoyaCommented:

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)  
0
 
TECH_NETAuthor Commented:
ewangoya: i do not see any changes to the query in the posting you made. It is exactly similar to the one i posted.
0
 
Ephraim WangoyaCommented:
Look. At the last line onsubject id
0
 
LowfatspreadConnect With a Mentor Commented:
SELECT * FROM <TableNAME>
WHERE (        CLASS_ID IN (SELECT CLASS_ID FROM STUDENT_MANAGEMENT_REGISTRATION)
             AND STUDENT_ID=@STUDENT_ID
           )
      OR @STUDENT_ID=-1  
0
 
sarabhaiCommented:
Is it like on Parameter studentid You need to find its class and then from that class display Studentid -1 and passing studentid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.