Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[*MAFIA* Capsloc]

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.

Code: [Select]

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.

Code: [Select]

/* 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)



[*MAFIA* Capsloc]

Nvm balth.  Fix't.

Code: [Select]

SELECT S.Student_ID, Last_Name, First_Name, Course_Name, E.Department_Name,
E.Course_Number, Section_Letter
FROM ((dbo.Student S
INNER JOIN dbo.Enroll E
ON E.Student_ID = S.Student_ID)
INNER JOIN dbo.Course C
ON E.Department_Name = C.Department_Name)

Had my 2nd join wrong

[*MAFIA* Bonehead]

do a table, and call info off it instead, of a single directive call. also headering or cookie the info, that will make it even easier to call.

[*MAFIA* Capsloc]

yeah calling the table would have been easier, but it was for an assignment.  got it all to work tho.

[*MAFIA* Bonehead]

glad you got it working php is an amazing code.