A Very Difficult Query I Need to Preform In Order to Create My Model

Question

So in my project i need to access a table called 'Dates'. Table 'Dates' contains the following:

ClassID | TeacherID | StudentID | DateInfo | Status | DateID

To any of the IDs, except DateID, I need to create a model connected to the Dates Model. Another Important Piece of information is that I have a table called 'Users' and 2 tables derive from 'Users', 'Students' and 'Teachers'.

My Problem: because I need both the student and the teacher in the same record, I need to request from the DB the user's information about both of them in the same query. I can not do that because of i don't know how can I define the student's information different from the teacher information.

Users Table:

UserID | Name | LastName | Password | Status | Role 

Teachers Table :

TeacherID | ClassID

Students Table :

StudentID | YearAndClass

The query I'm trying to create :

SELECT
IF(Users.Role == 0(Student Role))
  Users.Name as sName, ....
ELSE IF(Users.Role == 1(Teacher Role))
  Users.Name as tName, ....  
FROM Dates, Users, Students, Teachers

I know that won't work, but I'm not sure how else can I describe it.

Thanks in advance!

Answer 1

As per my comment on your question, I do still think you should go back and rework your structure slightly, you seem to be mostly there but it is better to have the foundations of any process correct before using it.

That said, from what I understand, you want to get the User Names in 1 list but distinguishable by their role. the below should work.

I have added in UserIDs and the Role field so that you can check them against your data to see if it is working as expected.

    SELECT
     CASE   WHEN d.Role = 'Teacher Role' THEN ut.UserID
            WHEN d.Role = 'Student Role' THEN us.UserID
            ELSE ''
        END AS UserID
    ,CASE   WHEN d.Role = 'Teacher Role' THEN ut.Name
            WHEN d.Role = 'Student Role' THEN us.Name
            ELSE ''
        END AS UserName
    ,d.Role
    FROM Dates d
    LEFT JOIN Users ut ON d.TeacherID = ut.UserID
    INNER JOIN Teachers t ON ut.UserID = t.TeacherID
    LEFT JOIN Users us ON d.StudentID = us.UserID
    INNER JOIN Students s ON us.UserID = s.StudentID

or possibly the below (untested);

    SELECT
     CASE   WHEN d.Role = 'Teacher Role' THEN ut.UserID
            WHEN d.Role = 'Student Role' THEN us.UserID
            ELSE ''
        END AS UserID
    ,CASE   WHEN d.Role = 'Teacher Role' THEN ut.Name
            WHEN d.Role = 'Student Role' THEN us.Name
            ELSE ''
        END AS UserName
    ,d.Role
    FROM Dates d
    LEFT JOIN Users ut ON d.TeacherID = ut.UserID AND ut.UserID IN (SELECT DISTINCT TeacherID FROM Teachers)
    LEFT JOIN Users us ON d.StudentID = us.UserID AND us.UserID IN (SELECT DISTINCT StudentID FROM Students)

Answer 2

So as I said, I'm doing a project. My project is a system to manage parent meeting's day in school. Those are my tables, relationships, and such :

My goal was to create a "getAllDates" function in my API.

In my DB, the Dates table is where all the "teacher-parent meetings" information stored. As a result of that, my Dates table contains A bunch of foreign keys, that in my API code are translated to instances of other models in the Dates model, as seen here:

namespace ProjAPI.Models
{
    public class Date
    {
        public DateTime DateInfo;
        public Student Student;
        public Teacher Teacher;
        public Classroom Classroom;
        public int Status, Id;
    }
}

As you imagine, each of the instances have properties of its own, properties i need to supply him. That's when my problem begun.

So let's take a look on the other models:

User Model

namespace ProjAPI.Models
{
    public class User
    {
        public string Name, LastName, Address, Phone, Pass, Email, Id;
        public int Role, Status;
    }
}

Student Model

namespace ProjAPI.Models
{
    public class Student : User
    {
        public string YearAndClass;
    }
}

Teacher Model

namespace ProjAPI.Models
{
    public class Teacher : User
    {
        public Classroom Classroom;
    }
}

Classroom Model

namespace ProjAPI.Models
{
    public class Classroom
    {
        public Building Building;
        public string Id;
        public string Name;
    }
}

Building Model

namespace ProjAPI.Models
{
    public class Building
    {
        public string Id;
        public int RoomNum;
    }
}

So, I needed a query that would : 1. take all the information for the teacher and the student and will rename the columns so i can access them in the code. 2. take all of the information about the classes, and distinct them so i can access each class separately. 3. take all of the information about the date.

In order to solve that, i created temporary tables within my query:

(
    SELECT 
        Classes.ClassID as cID, Classes.ClassName as cName, 
        Buildings.BuildingID as bID, Buildings.RoomNum 
    FROM 
        Classes 
    FULL OUTER JOIN 
        Buildings 
    on 
        Classes.BuildingID  = Buildings.BuildingID
) as cb,

cb is a temp table that represent the class in my Dates table.

(
    SELECT 
        Buildings.RoomNum as tcRoomNum,
        Classes.ClassID as tcID, Classes.BuildingID as tcbID,
        Classes.ClassName,ut.*
    FROM 
        Classes,Buildings,
        (
            SELECT 
                UserID as tID, Name as tName, LastName as tLastName,
                Status as tStatus, Role as tRole, Phone as tPhone,
                Email as tEmail, Address as tAddress,Password as tPassword,
                 ClassID as clID
            FROM 
                Users 
            LEFT JOIN 
                Teachers 
            ON 
                UserID = TeacherID
        ) as ut 
    WHERE 
        ut.clID=Classes.ClassID 
        AND Classes.BuildingID = Buildings.BuildingID
) as utcb,

utcb is a temp table to represent the teacher in my Dates table.

(
    SELECT 
        UserID as sID, Name as sName, LastName as sLastName,
        Status as sStatus, Role as sRole, Phone as sPhone,
        Email as sEmail, Address as sAddress, Password as sPassword,
         YearAndClass
     FROM 
     Users 
     LEFT JOIN 
        Students 
     ON 
        UserID = StudentID
) as us

us is a temp table to represent the student in my Dates table.

Now, when I had created the temp tables, i accessed them and filter correctly:

SELECT 
    DateInfo,DateID,Dates.Status as dStatus,us.*,utcb.*,cb.* 
FROM 
    Dates,cb,utcb,us
WHERE
Dates.StudentID = us.sID 
AND Dates.TeacherID = utcb.tID
AND Dates.ClassID = cb.cID

I know I did it bad and inefficient, but I did it alone and it was tough and I'm proud of my self. If anyone have any suggestions to upgrade this query feel free to let me know.