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

Query syntax to return a record for EVERY value in another table

I need help with the syntax for a query to return a record for EVERY student for EVERY semester that exists that tells me if they are registered or not.

Table "SEMESTER" contains a list of every semester available for registration.

SEMESTER
session (pk)
SemName
SemYr

Table "REGISTRATION" contains a row for each semester a student is registered for.  If they are not registered for a given semester, they do not have a row.
REGISTRATION
RegID (pk)
StudentId
Session

My result should be like;
studentid         session             reg
0001               1                      y
0001               2                      n
0002               1                      y
0002               2                     n
0003               1                     n
0003               2                      n

I figured this should be simple but am struggling to get the null records to pull in.  I've tried different joins and sub-queries with no luck.  Does Access support "FOR EACH" or "FOR EVERY" syntax?

Thanks for your help.
0
Jinghui Li
Asked:
Jinghui Li
  • 4
  • 3
  • 2
  • +1
1 Solution
 
data_n_pokerCommented:
Have you tried just doing this?
Select r.studentid,  r.session,  r.reg
From SEMESTER s , REGISTRATION r
Where s.session = r.Session

Open in new window

0
 
pdd1lanCommented:
do you have table for Students?

you have a registration table that contains only students registered for. but to be to show the one that not register, you might need to have a student table
0
 
pdd1lanCommented:
assume you have students table with StudentID field.  query might something like below.

sql:
SELECT Students.StudentID, REGISTRATION.Session,
Reg=case when IsNull(REGISTRATION.[StudentId]) then 'n'
else 'y' end  
FROM Students LEFT JOIN (REGISTRATION LEFT JOIN SEMESTER ON REGISTRATION.Session = SEMESTER.session) ON Students.StudentID = REGISTRATION.StudentId

access:
SELECT Students.StudentID, REGISTRATION.Session, IIf(IsNull(REGISTRATION.[StudentId]),"n","y") AS Reg
FROM Students LEFT JOIN (REGISTRATION LEFT JOIN SEMESTER ON REGISTRATION.Session = SEMESTER.session) ON Students.StudentID = REGISTRATION.StudentId;
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jinghui LiAuthor Commented:
data n poker:  This query is not bringing in sessions where they are not registered.  It is only showing those they are registered for.

pdd1lan:  Yes, I do have a STUDENTS table.  Your query is getting me closer.   I had to alter the FROM clause as follows, but I think that was what you intended Access to do.

FROM (Students LEFT JOIN REGISTRATION ON Students.StudentID = REGISTRATION.StudentId) LEFT JOIN SEMESTER ON REGISTRATION.Session = SEMESTER.session ;

So this query will bring in one row if the student does not exist is the REGISTRATION table with Reg="N".  But it does not bring in one row for EVERY semester they are not registered.  Likewise, if a student is only registered in one semester, it doesn't show the ones they are not registered in.

I will keep working from this start, but I welcome any additional suggestions.
0
 
SharathData EngineerCommented:
Can you post some sample data for three tables with expected result.
0
 
Jinghui LiAuthor Commented:
Certainly.

I've attached an excel file with four sheets; "SEMESTER", "STUDENT", "REGISTRATION" (the three tables), and then "Result" to show the query I am trying to produce.

Thanks in advance for any help!!
result.xlsx
0
 
SharathData EngineerCommented:
try like this.
SELECT s.StudentID, 
       s.[Session], 
       CASE 
         WHEN r.RegID IS  NULL THEN 'N' 
         ELSE 'Y' 
       END AS Reg 
  FROM (SELECT s1.StudentID, 
               s2.[Session] 
          FROM STUDENT AS s1, 
               SEMESTER AS s2) AS s 
       LEFT JOIN REGISTRATION AS r 
         ON s.StudentID = r.StudentID 
            AND s.[Session] = r.[Session]

Open in new window

0
 
Jinghui LiAuthor Commented:
PERFECT!!!  Thank you so much!

Since Access syntax doesn't seem to support CASE statements, I had to change it to an IIF statement - but it gave me exactly the result I was looking for.

In my attempts I was trying to do the cartesian join with the SEMESTER table at the end, but doing it first (as S) was definitely how it needed to be done.

Again, many thanks for your help.
0
 
SharathData EngineerCommented:
Glad I could help you. As I do not know about your database, I have implemented this in SQL Server. Yea, With IIF, it will work in Access.
0
 
Jinghui LiAuthor Commented:
Please close the discussion.
Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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