SQL students mark query

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

How can I select marks of a student who not marked in some sub lessons

edu_submark:
Id      sm_mark sub_lesson_id    st_id  course_id
1       97       1               9      3
2       66       2               9      3
3       22       2               1012   3
4       32       1               1012   3
1002    15       1               13     3

edu_sub_lesson:
Id      sl_title            lesson_id
1       Active Directory        2
2       Win7                    2
2009    UI                      1
2011    Win SERVER 2008 R2      2

I used this query:

SQL

SELECT 
    a.sl_title, b.sm_mark 
FROM  
    edu_sublesson a 
LEFT JOIN
    edu_submark b on a.Id = b.sub_lesson_id 
WHERE
    lesson_id = 2 AND course_id = 3 AND st_id = 9

result:

Active Directory 99.9
Win              75

But I want this result:

Active Directory 99.9
Win Server       NULL
Win              75
Administration   NULL

I want to show marks field in a gridView so teacher can see or edit student marks,
we have course table that have a fk of lesson tbl,
sub_lesson tbl have a fk of lesson,
sub_marks have 3 fk of student,sub_lesson ,course tables

Posted 17-Oct-14 3:31am

takpar

Updated 17-Oct-14 5:24am

Maciej Los

v3

Add a Solution

Comments

Herman<T>.Instance 17-Oct-14 9:51am

you where clause is the cause

takpar 17-Oct-14 10:03am

I don't understand!

Herman<T>.Instance 17-Oct-14 10:16am

new Hint: NULL is not 2, 3 or 9

Herman<T>.Instance 17-Oct-14 10:17am

Another HINT: LEFT OUTER JOIN in stead op LEFT JOIN

takpar 17-Oct-14 10:29am

Active Directory 97
Win7 99.5

but the result was same :(

takpar 17-Oct-14 10:37am

I want to show marks field in a gridView, so teacher can see or edit student marks,
we have course table that have a fk of lesson tbl,
sub_lesson tbl have a fk of lesson,
sub_marks have 3 fk of student,sub_lesson ,course tables

3 solutions

Solution 1

You haven't used table aliases in your query so it's quite hard to say which column originates frmo which table. But anyhow the resttricting columns from b should be allowed to be NULL.

For example if st_id comes from edu_submark, then modify the query as follows:

SQL

SELECT 
    a.sl_title, b.sm_mark 
FROM  
    edu_sublesson a 
LEFT JOIN
    edu_submark b on a.Id = b.sub_lesson_id 
WHERE lesson_id = 2 
AND course_id = 3 
AND ( st_id = 9 OR st_id IS NULL)

Posted 17-Oct-14 4:03am

Wendelius

Comments

takpar 17-Oct-14 10:30am

Active Directory 97
Win7 99.5

but the result was same :(

Wendelius 17-Oct-14 10:44am

Could you post example data for both tables?

takpar 17-Oct-14 11:06am

OK

Wendelius 19-Oct-14 6:20am

In order to build the query correctly we should know from which table the following columns are coming:
- lesson_id
- course_id
- st_id

The columns from edu_submark whould be allowed to have a NULL value in the result since this is an outer join.

Maciej Los 17-Oct-14 11:26am

Mika, the question has been updated ;)

PS.
Nice to "see" you ;)

Cheers,
Maciej

Wendelius 19-Oct-14 6:20am

Nice to see you too :)

Maciej Los 19-Oct-14 6:23am

Where have you been so long? How are you, Mika?

Wendelius 19-Oct-14 6:44am

Thanks for asking. I've been participating with CodeProject a lot but perhaps not in a visible way :) How are you?

Maciej Los 19-Oct-14 6:49am

Thank you, i'm fine. I had 2 months of pause (haven't enough time to visit CodeProject). Now, i,m back ;)

Wendelius 19-Oct-14 7:04am

Glad to have you back :)

Solution 4

Instead of where clause add all conditions in Left Join

SQL

SELECT
    a.sl_title, b.sm_mark
FROM
    edu_sublesson a
LEFT JOIN
    edu_submark b on a.Id = b.sub_lesson_id
    AND lesson_id = 2 
    AND course_id = 3 
    AND st_id = 9

Posted 27-Oct-14 22:24pm

Arora_Ankit

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Babu Mahimairaj · Accepted Answer · 2014-10-19T21:45:00

SQL

--Declare Teamp table for sub mark

DECLARE @edu_submark AS TABLE (Id  INT IDENTITY(1,1),sm_mark INT,sub_lesson_id  INT,st_id INT ,course_id INT)

-- insert data into sub mark

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES ( 97,1,9,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES ( 66,2,9,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES (22,2,1012,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES ( 32,1,1012,3)

INSERT INTO @edu_submark (sm_mark,sub_lesson_id,st_id,course_id) VALUES (15,1,13,3)

--Declare Teamp table for sub lesson

DECLARE @edu_sub_lesson AS TABLE (Id  INT IDENTITY(1,1), sl_title  NVARCHAR(30), lesson_id INT)


-- insert data into sub lesson

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('Active Directory', 2)

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('Win7', 2)

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('UI', 1)

INSERT INTO @edu_sub_lesson(sl_title,lesson_id) VALUES ('Win SERVER 2008 R2 ', 2)


--Get Data

SELECT
    a.sl_title, ISNULL(b.sm_mark,0)  AS sm_mark
FROM
  @edu_sub_lesson  a
LEFT OUTER JOIN
 @edu_submark b     on a.Id = b.sub_lesson_id