Solved

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

Posted on 2011-03-25
10
262 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
  • 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
 

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 40

Expert Comment

by:Sharath
ID: 35229218
Can you post some sample data for three tables with expected result.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 40

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 40

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

747 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

11 Experts available now in Live!

Get 1:1 Help Now