Solved

Help with query - subquery?

Posted on 2016-10-29
16
42 Views
Last Modified: 2016-11-11
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
Comment
Question by:arcross
  • 5
  • 5
  • 3
  • +1
16 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41865439
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
 
LVL 8

Author Comment

by:arcross
ID: 41865447
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41865460
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
 
LVL 8

Author Comment

by:arcross
ID: 41865470
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41865472
The DB  relations come next
First list few records and show the required output
Try to upload that database
0
 
LVL 8

Accepted Solution

by:
arcross earned 0 total points
ID: 41865474
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41865487
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 8

Author Comment

by:arcross
ID: 41865495
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41865498
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41865571
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
 
LVL 8

Author Comment

by:arcross
ID: 41865771
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41865821
Is the issue sorted out?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41866405
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
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41871711
@Author - Any feedback on this?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41883257
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now