Re: Insert with multiple subqueries - possible?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/15/05 

Date: Sat, 15 Jan 2005 00:16:24 -0500

John Dinning wrote:
> Firstly, the purpose of my question originally was simply to find out
> if it was possible to use multiple subqueries in an insert statement
> (particularly in MSAccess), and if so, what the syntax is.
> I can do what I need to do with 3 queries, but not knowing how to do
> it in one is bugging me.
>
> Secondly, I have now become concerned that either I am going about
> this all the wrong way (quite possible), or I have failed to explain
> myself properly. Let me try again.
>
>
> I have a Course table (lets say with 3 fields):
> Course_ID (AutoNumber - unique)
> Course_Code (Text - also Unique)
> Course_Description (Text)
>
> Sample Data:
> 1 'BIO' 'Biology'
> 2 'MAT' 'Mathematics'
> 3 'GEO' 'Geology'
>
>
> I have a Student Table (lets say it also has 3 fields):
> Stu_ID (AutoNumber - unique)
> Stu_Number (Text - also Unique)
> Stu_Name(Text)
>
> Sample Data:
> 1 '1234' 'Fred'
> 2 '2323' 'Harry'
> 3 '765' 'Mary'
> 4 '99999' 'Jane'
>
>
> The third table, CourseStudent, has three fields:
> SC_ID (AutoNumber - unique - not really necessary, I have been
> advised that every table should have one but in this instance I see
> no reason for it) SC_CourseID (the Course_ID from the Course table)
> SC_StuID (the Stu_ID from the Student table)
>

Maybe this will help. The example has some restrictions on it (see
constraints on all tables). If the conditions in the example, do not
meet your conditions, you may have to restrict the rows in the final
insert by comaring to the existing tables or using DISTINCT.

Drop Table Student
Create Table Student (Stu_ID int identity PRIMARY KEY CLUSTERED,
Stu_Number int not null)
Create unique index Student_Number_IDX on Student (Stu_Number)
Insert Into Student Values (1000)
Insert Into Student Values (2000)
Insert Into Student Values (3000)

Drop Table Course
Create Table Course (Course_ID int identity PRIMARY KEY CLUSTERED,
Course_Number int not null)
Create unique index Course_Number_IDX on Course (Course_Number)
Insert Into Course Values (10000)
Insert Into Course Values (20000)
Insert Into Course Values (30000)

Drop Table Student_Course
Create Table Student_Course (Stu_ID int NOT NULL REFERENCES Student,
Course_ID int NOT NULL REFERENCES Course)
Insert Into Student_Course Values (1,1)
Insert Into Student_Course Values (2,1)
Insert Into Student_Course Values (3,2)
Insert Into Student_Course Values (3,3)

Drop Table ImportStudents
Create Table ImportStudents (Stu_Number INT NOT NULL, Course_Number INT
NOT NULL PRIMARY KEY CLUSTERED (Stu_Number, Course_Number))
Insert Into ImportStudents Values (1000,20000)
Insert Into ImportStudents Values (3000,10000)

Select * from Student_Course
Stu_ID Course_ID
----------- -----------
1 1
2 1
3 2
3 3

Select * from ImportStudents

Insert Into Student_Course (
       Stu_ID, Course_ID )
Select a.Stu_ID,
       b.Course_ID
>From ImportStudents c inner join Course b
       on c.Course_Number = b.Course_Number
       inner join Student a
       on c.Stu_Number = a.Stu_Number

Select * from Student_Course

Stu_ID Course_ID
----------- -----------
1 1
2 1
3 2
3 3
1 2
3 1 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: Array Help
    ... >> How can I make an array in the student ... >> using the public void recordMarks(Module m, int eMark, int cMark) ... you could set up an array of type ModuleRecord. ... private int courseMark; ...
    (comp.lang.java.help)
  • Re: Insert with multiple subqueries - possible?
    ... > John Dinning wrote: ... > Create Table Student (Stu_ID int identity PRIMARY KEY CLUSTERED, ... > Insert Into ImportStudents Values ... > From ImportStudents c inner join Course b ...
    (microsoft.public.sqlserver.programming)
  • Re: Pass-by-reference to nested function?
    ... Tricky, that, in C. C uses pass-by-value for all parameter-passing. ... Let's take a look at the student object, then, shall we? ... In C, main returns int. ... That pointer sure looks to me as if it's being passed by value. ...
    (comp.lang.c)
  • Re: newbie, querie help/advice
    ... It is the owner of the object. ... qualify object name with owner and can also improve performance. ... >>> CREATE TABLE STUDENT ... >>> Student_ID int NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Array Help
    ... OK, so I've got three classes; Module, ModuleRecord and Student. ... courseworkWeight creating a Module instance. ... using the public void recordMarks(Module m, int eMark, int cMark) method? ... private int semester1; ...
    (comp.lang.java.help)