Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with SQL Procedure Query

Posted on 2011-02-13
9
Medium Priority
?
195 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:TECH_NET
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34884150
>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
 

Author Comment

by:TECH_NET
ID: 34884335
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
 

Author Comment

by:TECH_NET
ID: 34884353
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:TECH_NET
ID: 34884357
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34884923

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
 

Author Comment

by:TECH_NET
ID: 34885069
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34885079
Look. At the last line onsubject id
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 total points
ID: 34885926
SELECT * FROM <TableNAME>
WHERE (        CLASS_ID IN (SELECT CLASS_ID FROM STUDENT_MANAGEMENT_REGISTRATION)
             AND STUDENT_ID=@STUDENT_ID
           )
      OR @STUDENT_ID=-1  
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 34886874
Is it like on Parameter studentid You need to find its class and then from that class display Studentid -1 and passing studentid
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question