Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with query - subquery?

Posted on 2016-10-29
16
Medium Priority
?
49 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 31

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 31

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 31

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

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 31

Expert Comment

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

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 30

Expert Comment

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

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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