Solved

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

Posted on 2011-03-25
10
277 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Subquery bad Join 11 59
Insert with SET how to handle join 6 57
How does this SELECT query work 11 100
Update data using formula 22 20
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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

22 Experts available now in Live!

Get 1:1 Help Now