Balth i'm sure you are the one for this one....here it goes
So i inserted all my data and stuff to some tables that were given to us...all goes fine until the Multiple Table Inner joins come in.
I have to get Student_ID, Last_Name, First_Name, Course_Name, Department_Nam e, Course_Number, Section_Letter for every student enrolled in every section. Order by Student_ID.
My 3 tables that i will pull this from are
dbo.Enroll dbo.Student dbo.Course
this was my join. Still trying to figure out how they fully work on multiple tables.
SELECT Student_ID, Last_Name, First_Name, Course_Name, Department_Name,
Course_Number, Section_Letter
FROM ((dbo.Student S
INNER JOIN dbo.Enroll E
ON E.Student_ID = S.Student_ID)
(dbo.Student S
INNER JOIN dbo.Course C
ON S.Department_Name = C.Department_Name)
But that was pure fail:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Department_Nam e'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'Student_ID'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'Department_Nam e'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'Course_Number'.
Here is my SQL Script if you need it.
/* Step 0
Filename: CS61_4_Tamayo_Chris.SQL
Date: 5/05/2009
Backup: Flash Drive
*/
USE CS61db_28
IF EXISTS (select * from dbo.sysobjects
WHERE id = object_id(N'dbo.Enroll') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Enroll
GO
IF EXISTS (select * from dbo.sysobjects
WHERE id = object_id(N'dbo.Section') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Section
GO
IF EXISTS (select * from dbo.sysobjects
WHERE id = object_id(N'dbo.Student') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Student
GO
IF EXISTS (select * from dbo.sysobjects
WHERE id = object_id(N'dbo.Course') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.Course
GO
/* Create tables now */
CREATE TABLE dbo.Course (
Department_Name varchar(20) NOT NULL,
Course_Number int NOT NULL,
Course_Name varchar(30) NOT NULL,
Course_Description varchar(60) NOT NULL,
Credit_Hours int NOT NULL DEFAULT 3
)
GO
CREATE TABLE dbo.Section (
Course_Number int NOT NULL,
Department_Name varchar(20) NOT NULL,
Section_Letter char(1) NOT NULL DEFAULT 'A',
Classroom varchar(10) NOT NULL,
Instructor_ID int not null
)
GO
CREATE TABLE dbo.Student (
Student_ID int IDENTITY (1000,10) not null,
Last_Name varchar(20) NOT NULL,
First_Name varchar(20) NOT NULL,
City varchar(20) DEFAULT 'Santa Monica',
State char(2) DEFAULT 'CA',
)
GO
CREATE TABLE dbo.Enroll (
Department_Name varchar(20) NOT NULL,
Course_Number int NOT NULL,
Section_Letter char(1) NOT NULL DEFAULT 'A',
Student_ID int NOT NULL
)
GO
/* Create Primary Keys */
ALTER TABLE dbo.Course WITH NOCHECK ADD
CONSTRAINT PK_DptName_CourseName PRIMARY KEY CLUSTERED
(Department_Name,Course_Number)
GO
ALTER TABLE dbo.Student WITH NOCHECK ADD
CONSTRAINT PK_StudentID PRIMARY KEY CLUSTERED
(Student_ID)
GO
/* Create Foreign Keys */
ALTER TABLE dbo.Enroll ADD
CONSTRAINT FK_Student_ID FOREIGN KEY
(Student_ID) REFERENCES dbo.Student (Student_ID)
GO
ALTER TABLE dbo.Section ADD
CONSTRAINT FK_DeptName_CNum_section FOREIGN KEY
(Department_Name,Course_Number) REFERENCES dbo.Course (Department_Name,Course_Number)
GO
ALTER TABLE dbo.Enroll ADD
CONSTRAINT FK_DptName_CourseNum FOREIGN KEY
(Department_Name,Course_Number) REFERENCES dbo.Course (Department_Name,Course_Number)
GO
/*Insert Data Into Tables */
INSERT INTO dbo.Student
(Last_Name,First_Name,City,State)
VALUES
('Tamayo','Chris','Bell Gardens', 'CA')
INSERT INTO dbo.Student
(Last_Name,First_Name,City,State)
VALUES
('Doe','John','Las Vegas', 'NV')
INSERT INTO dbo.Student
(Last_Name,First_Name,City,State)
VALUES
('Smith','James','Los Angeles', 'CA')
INSERT INTO dbo.Student
(Last_Name,First_Name,City,State)
VALUES
('Mazon','Amy','Boise', 'ID')
/* Insert Courses */
INSERT INTO dbo.Course
(Department_Name,Course_Number,Course_Name,Course_Description,Credit_Hours)
VALUES
('Math','101','Beginning Math', 'Middle School Algebra','3')
INSERT INTO dbo.Course
(Department_Name,Course_Number,Course_Name,Course_Description,Credit_Hours)
VALUES
('Psychology','210','Human Behavior', 'Course explains human reactions','2')
INSERT INTO dbo.Course
(Department_Name,Course_Number,Course_Name,Course_Description,Credit_Hours)
VALUES
('English','400','Comtemporary Literature', 'Another English Class','3')
/*Insert Sections*/
INSERT INTO dbo.Section
(Department_Name,Course_Number,Section_Letter,Classroom,Instructor_ID)
VALUES
('English','400','A', '215','15')
INSERT INTO dbo.Section
(Department_Name,Course_Number,Section_Letter,Classroom,Instructor_ID)
VALUES
('English','400','B', '216','16')
INSERT INTO dbo.Section
(Department_Name,Course_Number,Section_Letter,Classroom,Instructor_ID)
VALUES
('Math','101','B','75','10')
INSERT INTO dbo.Section
(Department_Name,Course_Number,Section_Letter,Classroom,Instructor_ID)
VALUES
('Math','101','C','74','11')
INSERT INTO dbo.Section
(Department_Name,Course_Number,Section_Letter,Classroom,Instructor_ID)
VALUES
('Psychology','210','A','220','2')
INSERT INTO dbo.Section
(Department_Name,Course_Number,Section_Letter,Classroom,Instructor_ID)
VALUES
('Psychology','210','D','225','1')
/* Insert Enrolls */
INSERT INTO dbo.Enroll
(Department_Name,Course_Number,Section_Letter,Student_ID)
VALUES
('Psychology','210','A','1010')
INSERT INTO dbo.Enroll
(Department_Name,Course_Number,Section_Letter,Student_ID)
VALUES
('Math','101','C','1010')
INSERT INTO dbo.Enroll
(Department_Name,Course_Number,Section_Letter,Student_ID)
VALUES
('English','400','B','1010')
INSERT INTO dbo.Enroll
(Department_Name,Course_Number,Section_Letter,Student_ID)
VALUES
('Math','101','B','1030')
INSERT INTO dbo.Enroll
(Department_Name,Course_Number,Section_Letter,Student_ID)
VALUES
('English','400','A','1030')
INSERT INTO dbo.Enroll
(Department_Name,Course_Number,Section_Letter,Student_ID)
VALUES
('Psychology','210','D','1000')
/* insert day and time rows*/
ALTER TABLE dbo.Section ADD
Class_Day varchar(15) NULL
ALTER TABLE dbo.Section ADD
Class_Time datetime NULL
/* Insert Times */
UPDATE dbo.Section
SET Class_Day = 'Monday', Class_Time = '10:00AM'
WHERE Department_Name = 'Math' AND Section_Letter = 'B'
UPDATE dbo.Section
SET Class_Day = 'Wednesday', Class_Time = '11:00AM'
WHERE Department_Name = 'Math' AND Section_Letter = 'C'
UPDATE dbo.Section
SET Class_Day = 'Tuesday', Class_Time = '05:00PM'
WHERE Department_Name = 'Psychology' AND Section_Letter = 'A'
UPDATE dbo.Section
SET Class_Day = 'Tuesday', Class_Time = '07:45AM'
WHERE Department_Name = 'Psychology' AND Section_Letter = 'D'
UPDATE dbo.Section
SET Class_Day = 'Friday', Class_Time = '09:00AM'
WHERE Department_Name = 'English' AND Section_Letter = 'B'
UPDATE dbo.Section
SET Class_Day = 'Saturday', Class_Time = '06:45AM'
WHERE Department_Name = 'English' AND Section_Letter = 'A'
SELECT * FROM dbo.Section
/* Step 2 */
SELECT Student_ID, Last_Name, First_Name, Course_Name, Department_Name,
Course_Number, Section_Letter
FROM ((dbo.Student S
INNER JOIN dbo.Enroll E
ON E.Student_ID = S.Student_ID)
(dbo.Student S
INNER JOIN dbo.Course C
ON S.Department_Name = C.Department_Name)