• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

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
0
arcross
Asked:
arcross
  • 5
  • 5
  • 3
  • +1
1 Solution
 
[ 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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now