How to insert various records from multiple tables with a condition

Question

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?

• Brendan McCaffrey: Please tag your post with the RDBMS you're using. As for you problem, you'll need a join condition. sqlshack.com/a-step-by-step-walkthrough-of-sql-inner-join Note, this is link is for SQL Server, since I do not know what RDBMS you're using.

Answer 1

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

• Ryan Gill: Thanks it works, I did think of this just didn't want to add a new column to the student table because I am handling much larger datasets and tables compared to the ones I gave in the question. 👍