0

I have 3 tables. ClassList, Student & Faculty. I'm trying to assign the faculty to a specific class, e.g. I'm assigning "John Doe" to the section "Section1" where this "Section1" exists in the Student table column.

Outcome:

Student table

 StudentID       Name    Section
    1            user1    Section1
    2            user2    Section1
    3            user3    Section2

Faculty table

 FacultyID       Name       Subject
    1            Faculty1   Subject ABC
    2            Faculty2   Subject DEF

ClassList table

  ClassListID        StudentID      FacultyID    ModifiedDate

My insert statement is like this:

INSERT INTO ClassList 
VALUES 
( 
    (
        SELECT Student.StudentID 
        FROM Student 
        WHERE Student.Section = 'Section1'
    ), 
    (
        SELECT Faculty.FacultyID 
        FROM Faculty 
        WHERE Faculty.FirstName = @FacultyName 
        OR Faculty.LastName = @FacultyName
    ), 
    GETDATE()
);

I know that the select statement in the Student table returning a lot so I'm wondering if there's a way to add all the return query of it into ClassList table?

0
2

If all students returned by the first select need to be combined with all faculties returned by the second select, then you just need to cross-join the two subsets and select from the resulting set. You can use either the classic comma join or the explicit CROSS JOIN syntax:

  1. Comma join:

    INSERT INTO
      dbo.ClassList (StudentID, FacultyID, ModifiedDate)
    SELECT
      s.StudentID
    , f.FacultyID
    , GETDATE()
    FROM
      (
        SELECT Student.StudentID 
        FROM dbo.Student 
        WHERE Student.Section = 'Section1'
      ) AS s
    , (
        SELECT Faculty.FacultyID 
        FROM dbo.Faculty 
        WHERE Faculty.FirstName = @FacultyName 
        OR Faculty.LastName = @FacultyName
      ) AS f
    ;
    
  2. CROSS JOIN:

    INSERT INTO
      dbo.ClassList (StudentID, FacultyID, ModifiedDate)
    SELECT
      s.StudentID
    , f.FacultyID
    , GETDATE()
    FROM
      (
        SELECT Student.StudentID 
        FROM dbo.Student 
        WHERE Student.Section = 'Section1'
      ) AS s
      CROSS JOIN
      (
        SELECT Faculty.FacultyID 
        FROM dbo.Faculty 
        WHERE Faculty.FirstName = @FacultyName 
        OR Faculty.LastName = @FacultyName
      ) AS f
    ;
    

Those variations are using your subselects without any change. You could also rewrite the query and cross-join the tables first before filtering both in the same WHERE:

INSERT INTO
  dbo.ClassList (StudentID, FacultyID, ModifiedDate)
SELECT
  s.StudentID
, f.FacultyID
, GETDATE()
FROM
  dbo.Student AS s
, dbo.Faculty AS f
-- or: CROSS JOIN dbo.Faculty AS f
WHERE
  s.Section = 'Section1'
  AND (f.FirstName = @FacultyName 
    OR f.LastName = @FacultyName)
;

Note that you need to put the OR-ed Faculty filters in brackets in order to combine them with the Student filter properly. Without the brackets the result would not match the desired logic due to precedence rules for the logic operators in Transact-SQL: AND would be evaluated first, OR second, while you would want it to be the other way round, hence the brackets.

0

Since you don't appear to have any common information in these two tables, I'd recommend doing a distinct cross apply:

INSERT INTO ClassList (StudentID, FacultyID, ModifiedDate)
SELECT DISTINCT student.studentID
    ,faculty.FacultyID
    ,getdate()
FROM Student
CROSS APPLY faculty
2
  • What does cross apply do? Yes, I am using SQL Server. My problem is the select statement I've made for the Students has a lot of results and can't be lessen by using distinction because it gives unique results. I want to add all of these results example 5 results to be added in the class list in one insert statement with the faculty. May 5 '15 at 17:01
  • Cross Apply is a valid answer, but you neglected to preserve OP's logic about which students and faculty to include. I would suggest expanding on your answer at least that much. Jul 20 '20 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.