How to insert various records from multiple tables with a condition



  • I am creating a relational database and I have 2 tables currently ( Staff and Student)

    Staff Looks Like this

    StaffID Class TotalPhonesConfiscated
    1 S201 1
    2 S202 0
    3 S203 1

    Student Looks like this

    StudentID Phone_Model
    1 iPhone7
    2 Samsung

    I have created another table called Phones

    I am trying to insert these specific records from both tables: StudentID and StaffID, Class, and Phone_Models, into the new table Phones. However it should only insert the records from the Staff table based on the condition if the staff's TotalPhonesConfiscated is >= 1.

    I have attempted but this does not provide the correct table:

    INSERT INTO Phones ( StudentID, 
                         StaffID, 
                         Phone_Model, 
                         Class
                       )
    SELECT   StudentID, 
             StaffID, 
             Phone_Model, 
             Class
    FROM Student, Staff
    WHERE TotalPhonesConfiscated >= 1;
    

    I can't figure out how to insert the records so it looks like each individual StaffID matches with each individual StudentID, it seems to work for the Student records but not for the Staff records. In theory what I want should look like this:

    StudentID StaffID Phone_Model Class
    1 1 iPhone7 S201
    2 3 iPhone11 S203

    Is there something missing from my SQL statement?



  • In theory what I want should look like this:

    | StudentID | StaffID | Phone_Model | Class |
    | --------- | ------- |------------ | ----- |
    |  1        | 1       | iPhone7     | S201
    |  2        | 3       | iPhone11    | S203
    

    Is there something missing from my SQL statement?

    You are missing relation between Staff and Student. You need a column StaffID int in the Student table.

    create table  Staff  (
    StaffID int ,
    Class varchar(10),
    TotalPhonesConfiscated int );
    

    insert into Staff values
    (1,'S201',1),
    (2,'S202',0),
    (3,'S203',1);

    create table Student (
    StudentID int ,
    Phone_Model varchar(20),
    StaffID int );

    insert into Student values
    (1,'iPhone7',1),
    (2,'Samsung',3);

    create table Phones (
    StudentID int,
    StaffID int ,
    Phone_Model varchar(20),
    Class varchar(10));

    Then you can insert the data:

    INSERT INTO Phones ( StudentID, 
                         StaffID, 
                         Phone_Model, 
                         Class
                       )
    SELECT   StudentID, 
             Student.StaffID, 
             Phone_Model, 
             Class
    FROM Student 
    INNER JOIN  Staff on Student.StaffID=Staff.StaffID
    WHERE TotalPhonesConfiscated >= 1;
    

    select * from Phones;

    Would result on:

    Result:

    StudentID StaffID Phone_Model   Class
    1           1       iPhone7     S201
    2           3       Samsung     S203
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2