need a little help and an example - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

need a little help and an example

i have information and stuff on doing joins but anyone got any good examples? can't seem to get it to work

Comments

  • noob_pronoob_pro Posts: 51Member
    : i have information and stuff on doing joins but anyone got any good examples? can't seem to get it to work
    : this is my code i need to show like a name of a student who their teacher is and what course it is for one table *shrugs*


    use master
    go

    drop database school
    go

    create database school
    go

    use school
    go

    create table students(
    SID int Primary key Identity (1,1),
    SFName varchar(20) not null unique,
    SLName varchar(20) not null unique,
    SPhone char(7) not null check (SPhone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or SPhone like ' '),
    Saddress varchar(20) not null
    );
    go

    insert into students values ('brian','Puettner','6994001','persianterrace');
    insert into students values ('chris','ollie','6980654','bear road');
    insert into students values ('dan','chapman','6687683','bladder');
    insert into students values ('eric','graber','7275568','snowshoe');
    insert into students values ('krystal','smith','7275548','berrystreet');
    go


    create table Faculty(
    FID int Primary key Identity (1,1),
    FFName varchar(20) not null ,
    FLName varchar(20) not null unique,
    FPhone char(7) not null check (Fphone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or FPhone like ' '),
    Faddress varchar(20) not null
    );
    go

    insert into faculty values ('Mr.','chan','4589000','buckleyroad');
    insert into faculty values ('Mr.','kohut','6991090','churchroad');
    insert into faculty values ('Mr.','sweeney','6871211','hamiltonroad');
    insert into faculty values ('Mr.','kamat','7276666','hamiltonroad');
    go

    create table Course(
    CID int primary key identity (1,1),
    CName varchar(20) not null
    );
    go

    insert into Course values ('java');
    insert into Course values ('c++');
    insert into Course values ('intro to pcs');
    insert into Course values ('linux');
    go

    create table teaching(
    FID int not null,
    CID int not null,
    Constraint Teaching_PK primary key(FID, CID),
    Constraint TEACHING_FID_FK FOREIGN KEY (FID)
    REFERENCES FACULTY(FID) ON DELETE CASCADE ON UPDATE CASCADE,
    Constraint TEACHING_CID_FK FOREIGN KEY (CID)
    REFERENCES COURSE(CID) ON DELETE CASCADE ON UPDATE CASCADE
    );

    insert into teaching values (1,1);
    insert into teaching values (2,2);
    insert into teaching values (3,3);
    insert into teaching values (4,4);
    go

    create table Classes(
    SID int not null,
    CID int not null,
    Constraint Classes_PK primary key (SID, CID),
    Constraint Classes_SID_FK FOREIGN KEY (SID)
    REFERENCES Students(SID) ON DELETE CASCADE ON UPDATE CASCADE,
    Constraint Classes_CID_FK FOREIGN KEY (CID)
    REFERENCES Course(CID) ON DELETE CASCADE ON UPDATE CASCADE
    );

    insert into classes values (1,1);
    insert into classes values (1,2);
    insert into classes values (2,2);
    insert into classes values (2,3);
    insert into classes values (3,3);
    insert into classes values (3,4);
    insert into classes values (4,3);
    insert into classes values (4,4);
    go

  • noob_pronoob_pro Posts: 51Member
    : : i have information and stuff on doing joins but anyone got any good examples? can't seem to get it to work
    : : this is my code i need to show like a name of a student who their teacher is and what course it is for one table *shrugs*
    :
    :
    : use master
    : go
    :
    : drop database school
    : go
    :
    : create database school
    : go
    :
    : use school
    : go
    :
    : create table students(
    : SID int Primary key Identity (1,1),
    : SFName varchar(20) not null unique,
    : SLName varchar(20) not null unique,
    : SPhone char(7) not null check (SPhone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or SPhone like ' '),
    : Saddress varchar(20) not null
    : );
    : go
    :
    : insert into students values ('brian','Puettner','6994001','persianterrace');
    : insert into students values ('chris','ollie','6980654','bear road');
    : insert into students values ('dan','chapman','6687683','bladder');
    : insert into students values ('eric','graber','7275568','snowshoe');
    : insert into students values ('krystal','smith','7275548','berrystreet');
    : go
    :
    :
    : create table Faculty(
    : FID int Primary key Identity (1,1),
    : FFName varchar(20) not null ,
    : FLName varchar(20) not null unique,
    : FPhone char(7) not null check (Fphone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or FPhone like ' '),
    : Faddress varchar(20) not null
    : );
    : go
    :
    : insert into faculty values ('Mr.','chan','4589000','buckleyroad');
    : insert into faculty values ('Mr.','kohut','6991090','churchroad');
    : insert into faculty values ('Mr.','sweeney','6871211','hamiltonroad');
    : insert into faculty values ('Mr.','kamat','7276666','hamiltonroad');
    : go
    :
    : create table Course(
    : CID int primary key identity (1,1),
    : CName varchar(20) not null
    : );
    : go
    :
    : insert into Course values ('java');
    : insert into Course values ('c++');
    : insert into Course values ('intro to pcs');
    : insert into Course values ('linux');
    : go
    :
    : create table teaching(
    : FID int not null,
    : CID int not null,
    : Constraint Teaching_PK primary key(FID, CID),
    : Constraint TEACHING_FID_FK FOREIGN KEY (FID)
    : REFERENCES FACULTY(FID) ON DELETE CASCADE ON UPDATE CASCADE,
    : Constraint TEACHING_CID_FK FOREIGN KEY (CID)
    : REFERENCES COURSE(CID) ON DELETE CASCADE ON UPDATE CASCADE
    : );
    :
    : insert into teaching values (1,1);
    : insert into teaching values (2,2);
    : insert into teaching values (3,3);
    : insert into teaching values (4,4);
    : go
    :
    : create table Classes(
    : SID int not null,
    : CID int not null,
    : Constraint Classes_PK primary key (SID, CID),
    : Constraint Classes_SID_FK FOREIGN KEY (SID)
    : REFERENCES Students(SID) ON DELETE CASCADE ON UPDATE CASCADE,
    : Constraint Classes_CID_FK FOREIGN KEY (CID)
    : REFERENCES Course(CID) ON DELETE CASCADE ON UPDATE CASCADE
    : );
    :
    : insert into classes values (1,1);
    : insert into classes values (1,2);
    : insert into classes values (2,2);
    : insert into classes values (2,3);
    : insert into classes values (3,3);
    : insert into classes values (3,4);
    : insert into classes values (4,3);
    : insert into classes values (4,4);
    : go
    :
    :
    ok on this code my classes table and my other table where i use numerals as values how do i set that up to use items from other tables ? i know its prolly simple like adding a quote in between or something which i tried and it didn't do anything suggestions? so instead of like insert into classes values (4,4) the names and such would come up instead of 4,4 i can only read so much :(
Sign In or Register to comment.