Solved

Help with SQL Procedure Query

Posted on 2011-02-13
9
185 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
9 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Expert Comment

by:ewangoya
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:ewangoya
ID: 34885079
Look. At the last line onsubject id
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now