Solved

Help with query - subquery?

Posted on 2016-10-29
16
40 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
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: 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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

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

Expert Comment

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

12 Experts available now in Live!

Get 1:1 Help Now