Help with query - subquery?

Hi,
I want to retrieve all records from table 'A' and I want to add another field from another table, 'B' in that query result.

for example:

Students                                     MarkingDetails
------------                                      ------------
Id                                                 id
StudentName                           StudentID
ClassId                                         Grade
                                                     MarkingID

This is what I have so far but it's not working right:

SELECT Students.ID, Students.StudentName, MarkingDetails.MarkingID, Students.ClassID, MarkingDetails.Grade, MarkingDetails.StudentID
FROM Students LEFT JOIN MarkingDetails ON Students.ID = MarkingDetails.StudentID
WHERE (((MarkingDetails.MarkingID) Is Null Or (MarkingDetails.MarkingID)=1) AND ((Students.ClassID)=1));

Basically what I would like is to get all the students and if the student exists in the second table, filtered by classid and markingid, get the grade.

thanks
LVL 8
arcrossAsked:
Who is Participating?
 
arcrossConnect With a Mentor Author Commented:
Hi, ive tried this and it seems to work but I don't know if it is the right way..

SELECT S.studentname,
(SELECT m.grade from markingdetails m where s.id = m.studentID and markingid =s.markingid) AS grade
FROM students AS S
where s.classid = 1
0
 
[ fanpages ]IT Services ConsultantCommented:
SELECT Students.ID, Students.StudentName, MarkingDetails.MarkingID, Students.ClassID, MarkingDetails.Grade, MarkingDetails.StudentID
FROM Students LEFT JOIN MarkingDetails ON Students.ID = MarkingDetails.StudentID
WHERE (((MarkingDetails.MarkingID) Is Null Or (MarkingDetails.MarkingID)=1) AND ((Students.ClassID)=1));

"LEFT JOIN MarkingDetails ON Students.ID = MarkingDetails.StudentID"

Do the values within the [ID] column of the [Students] table match the [StudentID] values in the [MarkingDetails] table, or is the [ID] column in both tables an auto-number or similar unique key?

That is, if, for example, there is a record of [Students].[ID] = 4, does the corresponding [MarkingDetails].[ID] = 4 record relate to the same Student?
0
 
arcrossAuthor Commented:
Thank you everybody for your answers.

Students.id will link to markingdetails.studentid.

Not always the markingdetailstable will have a corresponding student. Once an assignment has been marked for a student then a record will exist in tbe markingdetsils table.

The idea behind is to show all the students in a class and to see who s ones been marked (showing the grade) and who hasny showing a blank field
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
hnasrCommented:
Try this:
SELECT s.ID, s.StudentName, m.MarkingID, s.ClassID, m.Grade, m.StudentID
 FROM Students s INNER JOIN MarkingDetails m ON s.ID = m.StudentID
 WHERE WHERE (m.MarkingID)=1) AND (s.ClassID)=1)
UNION ALL
SELECT s.ID, s.StudentName, m.MarkingID, s.ClassID, m.Grade, m.StudentID
 FROM Students s LEFT JOIN MarkingDetails m ON s.ID = m.StudentID
 WHERE (m.StudentID) IS NULL

Open in new window

If still there is a misunderstanding, then list few input records and the required output.
0
 
arcrossAuthor Commented:
Capture.PNG
This is the db design.

fanpages, the query you suggested still leaves records out from the students table.
For instance, if I have a student with a different markindID (lets say 3) it will leave out the students with marking id 1

I dont know if something like this is possible...

select s.studentname, (SELECT grade from markingdetails where s.id = markingdetails.studentID) from  students as S where s.classid = 1 and markingid = [markingID]

get the grade from the second table if that student exists in the second table according to classid and markingid
0
 
hnasrCommented:
The DB  relations come next
First list few records and show the required output
Try to upload that database
0
 
hnasrCommented:
Hi, ive tried this and it seems to work but I don't know if it is the right way

Not sure, without input records and expected output, but the image of relations shows that Students has no markingid field.
0
 
arcrossAuthor Commented:
Hi hnasr,
I didnt put the markingid in that table because it only holds the students belonging to a class and each student will have multiple markings. This would give me lots of duplicates in the students table
0
 
hnasrCommented:
I understand that, but the query shows markingid as a field in Students (s.markingid)
=s.markingid) AS grade
 FROM students AS S

Open in new window

0
 
[ fanpages ]IT Services ConsultantCommented:
fanpages, the query you suggested still leaves records out from the students table.

I have not suggested a query.  I just re-posted your own from the question.
0
 
arcrossAuthor Commented:
Hi hnasr, my mistake, I meant to write it as a paramenter

"=[MyMarkingID]) AS grade
 FROM students AS S"

hi, fanpages, apologies, I read it quickly and I didnt realise it was my own ! :)
0
 
hnasrCommented:
Is the issue sorted out?
0
 
Pawan KumarDatabase ExpertCommented:
Try..

--
SELECT s.ID, s.StudentName, m.MarkingID, s.ClassID, m.Grade, m.StudentID
FROM Students s, MarkingDetails m WHERE s.ID = m.StudentID
AND ((m.MarkingID)=1) AND (s.ClassID)=1)) OR ((m.StudentID) IS NULL)

--

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Author - Any feedback on this?
0
 
Pawan KumarDatabase ExpertCommented:
Hi Author,

For the following reason: This is what at the end worked for me

Can you please inform what is worked for you. You have just posted the comment, code is missing. ?

Regards,
Pawan
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.