Solved

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

Posted on 2011-03-25
10
309 Views
Last Modified: 2013-11-05
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
Comment
Question by:Jinghui Li
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 1

Expert Comment

by:data_n_poker
ID: 35216875
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
 
LVL 8

Expert Comment

by:pdd1lan
ID: 35217132
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
 
LVL 8

Expert Comment

by:pdd1lan
ID: 35217214
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jinghui Li
ID: 35217661
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35229218
Can you post some sample data for three tables with expected result.
0
 

Author Comment

by:Jinghui Li
ID: 35232959
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
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35236615
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
 

Author Comment

by:Jinghui Li
ID: 35242574
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35242716
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
 

Author Comment

by:Jinghui Li
ID: 35242741
Please close the discussion.
Thanks,
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

717 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