Can't complete complicated query

Question

I have a question about an SQL in C# I want to implement but I'm a bit stuck. Below are the tables with some example data:

Student:

(PK)tagID  studentID (FK)courseID
4855755    HUJ564334    25
4534664    RED231232    33

Course

(PK)courseID courseName
25           Computer Science
33           Biology

CourseModule

(FK)courseID (FK)moduleID
25              CMP2343
25              CMP3456
33              BIO3422
33              BIO2217

Module

(PK)moduleID moduleName
CMP2343      Networking
CMP3456      Databases
BIO3422      Human body
BIO2217      Genetics

ModuleSession

(FK)moduleID (FK)sessionID
CMP2343      1ACMP2343
CMP2343      2ACMP2343 
CMP3456      1ACMP3456
CMP3456      2ACMP3456
BIO3422      1ABIO3422      
BIO3422      2ABIO3422
BIO2217      1ABIO2217      
BIO2217      2ABIO2217   

Session

(PK)sessionID sessionStartDate sessionTimeStart sessionTimeEnd
1ACMP2343      09/05/2013          12:00 AM         14:00 PM
2ACMP2343      05/05/2013          09:00 AM         11:00 PM
1ACMP3456      15/05/2013          12:00 AM         13:00 PM
2ACMP3456      01/05/2013          10:00 AM         13:00 PM
1ABIO3422      30/04/2013          11:00 AM         13:00 PM
2ABIO3422      01/04/2013          14:00 AM         16:00 PM
1ABIO2217      12/05/2013          16:00 AM         18:00 PM
2ABIO2217      03/05/2013          12:00 AM         14:00 PM   

Attendance

(FK)tagID (FK)sessionID scanningTime

I am using RFID scanner to scan the student tagID. I would like my query to find a "current" sessionID (by looking up the date and time of the session, saved in DB) for the given tagID, and display TagID and SessionID in the Attendance table, along with the time of scanning.

So to achieve this I need to:

• Look at what the Student table and get the TagID
• See which course this tagID is associated with (looking at CourseID column)
• Look at CourseModule table to see what modules are associated with the course
• Look at ModuleSession table to see what sessions are associated with the module
• Look at Session table to check which sessionID is happening "today and now"
• Display tagID and sessionID in Attendance table

So if I want to find the current session for the tagID = 4855755, then the Attendance output should be:

Attendance:

    (FK)tagID (FK)sessionID scanningTime
     4855755    1ACMP2343

This is because:

• Looking at Student table: student 4855755 is on courseID = 25
• Looking at CourseModule table: courseID = 25 compromises of two modules: CMP2343 and CMP3456
• Looking at ModuleSession table: modules CMP2343 and CMP3456 compromise of 2 sessions each: 1ACMP2343; 2ACMP2343 and 1ACMP3456; 2ACMP3456
• Looking at Session table: only session 1ACMP2343 is taking place today and now

My question is how is the query suppose to be written to be able to achieve the example mentioned above? I have made a start on the query but I need some help to get it working as it should be.

Code is:

SELECT Student.tagID, Session.sessionID 
FROM Student s , Session se
INNER JOIN Course c ON c.courseID = s.courseID
INNER JOIN CourseModule cm ON cm.courseID = c.courseID
INNER JOIN Module m ON m.moduleID = cm.moduleID
INNER JOIN ModuleSession ms ON ms.moduleID = cm.moduleID
INNER JOIN Session se ON se.sessionID = ms.sessionID
INNER JOIN Attendance a ON a.sessionID = se.sessionID 
WHERE Student.tagID = 4820427
AND s.SessionDate=DATE();

(updated) C# code of my class is:

    public void setSQL()
        {
            string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Kacper\\Desktop\\AutoReg\\AutoReg\\AutoReg.accdb;";`

            OleDbConnection MyConn = new OleDbConnection(ConnStr);
            MyConn.Open();

            DataSet ds = new DataSet();

            //query to ask
            string query = @"SELECT s.TagID, se.SessionID, " + "'" + 
DateTime.Now.ToString("MM/dd/yy HH:mm:ss tt") + @"' AS scanningTime 

                            FROM Student s,
                            CourseID-ModuleID cm,
                            ModuleID-SessionID ms,
                            Session se

                            WHERE 1=1 
                            AND s.TagID = 4820427
                            AND s.CourseID = cm.CourseID
                            AND ms.ModuleID = cm.ModuleID
                            AND ms.SessionID = se.SessionID
                            AND se.SessionDate = Date();";

            OleDbCommand command = new OleDbCommand(query, MyConn);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);

                adapter.Fill(ds);
                dataGridView2.DataSource = ds.Tables[0];
                MyConn.Close();

        }

I have updated my SQL code but still cannot get it running, I get and error: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Answer 1

I have managed to get this query work. It needed square brackets around the table names.

string query = @"SELECT s.TagID, se.SessionID, '" + 
           DateTime.Now.ToString("MM/dd/yy HH:mm:ss tt") + 
           "' AS scanningTime " + 
           "FROM (((Student s " + 
           " LEFT JOIN [CourseID-ModuleID] cm ON s.CourseID = cm.CourseID) " + 
           " LEFT JOIN [ModuleID-SessionID] ms ON ms.ModuleID = cm.ModuleID) " + 
           " LEFT JOIN [Session] se ON ms.SessionID = se.SessionID) " + 
           "WHERE s.TagID = 4820427 AND se.SessionDate = Date()";

Answer 2

Just to make your C# easier to read, you might want to use the "@" convention to insert newlines. I'm going to cheat for the third field -- I think you want the output to be the time you ran the query.

query = @"SELECT s.tagID, se.sessionID, " + 
    "'" + DateTime.Now.ToString("MM/dd/yy HH:mm:ss tt") + @"' AS scanningTime 
    FROM Student s
    INNER JOIN Course c ON c.courseID = s.courseID
    INNER JOIN CourseModule cm ON cm.courseID = c.courseID
    INNER JOIN Module m ON m.moduleID = cm.moduleID
    INNER JOIN ModuleSession ms ON ms.moduleID = cm.moduleID
    INNER JOIN Session se ON se.sessionID = ms.sessionID
    INNER JOIN Attendance a ON a.sessionID = se.sessionID 
    WHERE Student.tagID = 4820427
    AND se.SessionDate=DATE()";

There are actually a few ways to clean this up, I think. Do you need any value from Course? If not, drop that join. Same with module and module session.

query = @"select s.tagID, ms.sessionId, " + 
    "'" + DateTime.Now.ToString("MM/dd/yy HH:mm:ss tt") + @"' AS scanningTime 

    from student s,
    coursemodule cm,
    modulesession ms,
    session sess

    WHERE 1=1
    and s.tagID = 4820427
    and s.courseId = cm.courseId
    and ms.moduleId = cm.moduleId
    and ms.sessionId = sess.sessionId
    and sess.sessionStartDate = Date()";

Note that your SQL has an extra Session se in the same line as FROM Student s and has s.SessionDate instead of se.SessionDate.

Try blasting the comment in the second SQL; it's been a long time since I've thrown SQL at Access.

Past that, you're probably moving on to a different question. Try the standard debugging moves -- can you run a simpler query, like query = "SELECT * FROM Student"; from the same spot without the error?