?
Solved

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

Posted on 2011-03-25
10
Medium Priority
?
311 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 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