asked on

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

[ fanpages ]

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?

arcross

ASKER

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

Hamed Nasr

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.

arcross

ASKER

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

Hamed Nasr

The DB relations come next
First list few records and show the required output
Try to upload that database

ASKER CERTIFIED SOLUTION

arcross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

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.

arcross

ASKER

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

Hamed Nasr

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

[ fanpages ]

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.

arcross

ASKER

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

Hamed Nasr

Is the issue sorted out?

Pawan Kumar

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

Pawan Kumar

@Author - Any feedback on this?

Pawan Kumar

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