Solved

Help with SQL Procedure Query

Posted on 2011-02-13
9
187 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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: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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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