Re: Insert with multiple subqueries - possible?

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 01/15/05 

Date: Fri, 14 Jan 2005 20:44:17 -0500

John Dinning wrote:
> I am actually using MSAccess but I am sure the same will apply with
> SQL Server.

Not necessarily. JetSQL is not the same as Transact-SQL. you really should
post to an Access group
>
> I want to insert a record in a table, using values selected from 2
> other tables, but cannot find the right syntax.
> This is as close as I have come:
>
> INSERT INTO TblStuCourseDet
> (SCDet_CourseID,SCDet_StuID)
> VALUES
> ((SELECT Course_ID FROM TblCourse WHERE Course_Code = :Course_Code),
> (SELECT Stu_ID FROM TblStudent WHERE Stu_Num = :Stu_Num))
>

I'm reading ahead into the later posts, which have covered not using SELECT
in a VALUES clause. ..
OK, let's shoot ahead to this:
> 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.

Again, you'd be more likely getting an answer to a JetSQL question in an
Access newsgroup. however ...

> 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

Well, that advice was not, shall we say, correct.

> but in this instance I see
> no reason for it)

Good eye.

> SC_CourseID (the Course_ID from the Course table)
> SC_StuID (the Stu_ID from the Student table)
>
> A student may be on one or more courses, not all students are on all
> courses.

Ok so you're describing a many-to-many relationship resolved by a junction
table. We get it. We really do!

>
> I am doing a batch import (one record at a time)

That's a contradiction, of course ...

> from a text file
> which is a list of Student Numbers (Stu_Number) and Course Codes
> (Course_Code). E.g. 'BIO','Fred'
> I want to convert these Student Numbers and Course Codes to their
> respective ID's

OK, so you've already imported the records form the text file into the
respective Course and Student tables, correct? IOW, the sample data you give
above represents the data you imported from the text file(s)

> and create a record in the CourseStudent table,
> hopefully using the one Insert statement with 2 SubQueries as
> previously discussed (I can do it with 3 queries but am sure it must
> be possible to do it in one) - this is the query I cannot get to
> work.:
> ****************************************************************
> INSERT INTO CourseStudent
> (SC_CourseID,SC_StuID)
> SELECT
> (SELECT Course_ID FROM Course WHERE Course_Code = :Course_Code),
> (SELECT Stu_ID FROM Student WHERE Stu_Number = :Stu_Number)
> ****************************************************************
>
> Sample Data:
> 1 1 1 (Biology,Fred)
> 2 2 2 (Maths,Harry)
> 3 2 1 (Maths,Fred)
> 4 1 4 (Biology,Jane)
> 5 3 3 (Geology,Mary)
> 6 1 2 (Biology,Harry)
>
> From this table, for example, I can then get a list of all student
> names with a given course code:
> SELECT Student.Stu_Name
> FROM CourseStudent LEFT JOIN Student ON CourseStudent.SC_StuID =
> Student.Stu_ID
> WHERE SC_CourseID = (SELECT Course_ID FROM Course WHERE Course_Code
> =
>> Course_Code);
>
> Substituting the :Course_Code parameter with 'BIO' and running the
> query returns:
> Fred
> Jane
> Harry
>
> Similarly, I can get a list of course names for a given Student
> Number etc. I am using ID's in the Student and Course tables to avoid
> using Text fields in the Primary Index.
>
> I hope that makes sense now.

No. We all understand the reason for the junction table (and can see how
you're misusing it - you can do multiple joins, you know)

What is still unclear is how you are arriving at this particular set of
sample data, which I assume represents the data you wish to insert into the
CourseStudent table from the data in the Course and Student tables:

> Sample Data:
> 1 1 1 (Biology,Fred)
> 2 2 2 (Maths,Harry)
> 3 2 1 (Maths,Fred)
> 4 1 4 (Biology,Jane)
> 5 3 3 (Geology,Mary)
> 6 1 2 (Biology,Harry)

Why is Fred only linked with Biology and Maths? Why not Geology?
Same question for the Harry records.
Why is there only a single record apiece for Jane and Mary?

What is there in the data in Course and Student that is supposed to allow a
query to pick only these records to insert into CourseStudent?

To answer your question, if you can write and run a query to select the
records you wish to insert using the Access Query Builder, then you can use
the select statement as part of your INSERT...SELECT syntax. The problem is
creating a query to generate these particular 6 records.

Or did you shorten the sample data? Do you really wish to insert 12 records?
Like this?

INSERT INTO CourseStudent (SC_CourseID,SC_StuID )
SELECT Course_ID, Stu_ID
FROM Course, Student

Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: Query/Table question
    ... a make-table query is certainly not the way; ... field manually, and then populate it through a small, simple piece of VB ... sample data, I will be happy to provide some sample code. ... you would use the On Change event of the control holding the student ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Insert with multiple subqueries - possible?
    ... >> A student may be on one or more courses, not all students are on all ... the Insert query. ... > sample data, which I assume represents the data you wish to insert into ... the text file I am importing does this. ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert with multiple subqueries - possible?
    ... was possible to use multiple subqueries in an insert statement (particularly ... Sample Data: ... I have a Student Table: ... I want to convert these Student Numbers and Course Codes to their respective ...
    (microsoft.public.sqlserver.programming)
  • Re: Doing a "Select All" in Form
    ... > I have a form with student names and a select box. ... > nongraduates in the group. ... For a much more complex requirement I replaced looping through recordsets ... sample data to a second or less in the real world - and that included ...
    (microsoft.public.access.formscoding)
  • Re: Cumulative cost
    ... When you say "running total per student", I believe this means the same ... You can use a Totals query to get a "per student" Sum ... AND calculate something you're calling "cumulative cost". ...
    (microsoft.public.access.forms)