0

I am making a database with tv-shows and users. These users can follow each other and are saved in the database by an UserID. Now I wan't to create a view with the names of the users instead of the UserID's. --Drop all tables.

DROP TABLE Series CASCADE CONSTRAINTS;
DROP TABLE User1 CASCADE CONSTRAINTS;
DROP TABLE FollowingSeries CASCADE CONSTRAINTS;
DROP TABLE FollowingUser CASCADE CONSTRAINTS;
DROP TABLE Episode CASCADE CONSTRAINTS;
DROP TABLE Watched CASCADE CONSTRAINTS;
DROP VIEW Test CASCADE CONSTRAINTS;

--Create all tables.
CREATE TABLE Series
(   SeriesID NUMBER(5) primary key,
    Name VARCHAR2(100) NOT NULL,
    Status VARCHAR2(20),
    Premiered DATE,
    Genre VARCHAR2(100),
    Country VARCHAR2(100),
    Network VARCHAR2(100),
    Runtime VARCHAR2(25)
);

CREATE TABLE User1(
  UserID NUMBER(5) primary key,
  Username VARCHAR2(100) NOT NULL,
    Country varchar2(100),
    Gender VARCHAR2(10),
    Birthday VARCHAR2(100),
    About_me VARCHAR2(300),
    Password VARCHAR2(100) NOT NULL
);

CREATE TABLE FollowingSeries (
    UserID NUMBER(5) NOT NULL,
    SeriesID NUMBER(5) NOT NULL,
    Ignore1 CHAR(1) DEFAULT 0,
    constraint FollowingS_pk primary key(UserID, SeriesID),
    constraint FollowingS_fk1 foreign key(UserID) REFERENCES User1(UserID),
    constraint FollowingS_fk2 foreign key(SeriesID) REFERENCES Series(SeriesID)
);

CREATE TABLE FollowingUser (
    UserID1 NUMBER(5) NOT NULL,
    UserID2 NUMBER(5) NOT NULL,
    constraint FollowingU_pk primary key(UserID1, UserID2),
    constraint FollowingU_fk1 foreign key(UserID1) REFERENCES User1(UserID) ON DELETE SET NULL,
    constraint FollowingU_fk2 foreign key(UserID2) REFERENCES User1(UserID) ON DELETE SET NULL
);

CREATE TABLE Episode(
    SeriesID NUMBER(5) NOT NULL,
    Season NUMBER(2) NOT NULL,
    Episode NUMBER(2) NOT NULL,
    Name varchar2(100) NOT NULL,
    Airdate DATE,
    Summary varchar2(1500),
    constraint episode_pk primary key(SeriesID, Season, Episode),
    constraint episode_fk foreign key(SeriesID) REFERENCES Series(SeriesID) ON DELETE SET NULL
);

CREATE TABLE Watched(
    UserID NUMBER(5) NOT NULL,
    SeriesID NUMBER(5) NOT NULL,
    Season NUMBER(2) NOT NULL,
    Episode NUMBER(2) NOT NULL,
    constraint watched_pk primary key(UserID, SeriesID, Season, Episode),
    constraint watched_fk1 foreign key(UserID) REFERENCES User1(UserID),
    constraint watched_fk4 foreign key(SeriesID, Season, Episode) REFERENCES Episode(SeriesID, Season, Episode)
);



--Add users.
INSERT INTO User1 VALUES (1, 'StefPhilipsen', 'Netherlands', 'Male', TO_DATE('02-01-1994','DD-MM-YYYY'), 'Ik ben Stef', 'abcd1234');
INSERT INTO User1 VALUES (2, 'Dorothy142', 'America', 'Female', TO_DATE('01-10-1963','DD-MM-YYYY'), 'I love mountainbiking', 'Oow6sai4Ie');
INSERT INTO User1 VALUES (3, 'Rudo12', 'England', 'Male', TO_DATE('05-6-1985','DD-MM-YYYY'), 'I watched Breaking Bad and a lot more', 'Oph9Ge2yai');
INSERT INTO User1 VALUES (4, 'JohnSmith', 'England', 'Male', TO_DATE('27-9-1945','DD-MM-YYYY'), 'I like potatoes.', 'phiShaip0c');
INSERT INTO User1 VALUES (5, 'EulaWGibson', 'America', 'Female', TO_DATE('12-8-1990','DD-MM-YYYY'), 'I''m Eula. I live in massachusetts and I''m happily married with my husband Hank.', 'EiM5wii2am8');
INSERT INTO User1 VALUES (6, 'Pedro', 'Spain', 'Male', TO_DATE('12-10-1945','DD-MM-YYYY'), 'I love watching Arrow.', 'Gith0yaiw');
INSERT INTO User1 VALUES (7, 'TravisC', 'England', 'Male', TO_DATE('12-7-1970','DD-MM-YYYY'), 'My favorite TV-shows are Modern Family and Dexter.', 'gie9aiPh2Ii');
INSERT INTO User1 VALUES (8, 'DonellaScott', 'America', 'Female', TO_DATE('31-1-1996','DD-MM-YYYY'), 'Living the life.', 'Gued1996');

--Who follows which series.
INSERT INTO FollowingSeries(UserID, SeriesID) VALUES (1,1);
INSERT INTO FollowingSeries(UserID, SeriesID) VALUES (1,2);
INSERT INTO FollowingSeries(UserID, SeriesID) VALUES (1,3);
INSERT INTO FollowingSeries VALUES (1,4,1);

--Who follows who.
INSERT INTO FollowingUser VALUES(1,2);
INSERT INTO FollowingUser VALUES (2,3);
INSERT INTO FollowingUser VALUES (3,4);
INSERT INTO FollowingUser VALUES (1,3);
INSERT INTO FollowingUser VALUES (5,8);
INSERT INTO FollowingUser VALUES (5,6);
INSERT INTO FollowingUser VALUES (6,5);

-- Which episodes are watched by whom.
INSERT INTO Watched VALUES(1, 4, 1, 1);
INSERT INTO Watched VALUES(1, 4, 1, 2);
INSERT INTO Watched VALUES(1, 4, 1, 3);
INSERT INTO Watched VALUES(1, 4, 1, 4);
INSERT INTO Watched VALUES(1, 4, 1, 5);
INSERT INTO Watched VALUES(1, 4, 1, 6);
INSERT INTO Watched VALUES(1, 4, 1, 7);

This is what I tried but then I get an error message

--Create view
    CREATE VIEW follownaam AS
    SELECT followerUser.Username AS Follower, followingUser.Username AS Following
    FROM FollowingUser AS fu
        INNER JOIN User1 followerUser ON(fu.UserID1 = followerUser.Username)
        INNER JOIN User1 followingUser ON(fu.UserID2 = followingUser.Username)
        WHERE fu.UserID1 = 1;

[Err] ORA-00911: invalid character

The table I have exists of 2 UserID's. But now I want exactly the same table, but this view must contain the Usernames of these UserID's.

1
  • Beside the syntactic error. Why do you join Id to Username? Commented Nov 7, 2013 at 10:09

1 Answer 1

2

This should work (although i don't understand why You want a view only for user with ID = 1).

CREATE VIEW follownaam AS
SELECT 
followerUser.Username AS Follower, followingUser.Username AS Following
FROM FollowingUser fu
    INNER JOIN User1 followerUser ON(fu.UserID1 = followerUser.UserID)
    INNER JOIN User1 followingUser ON(fu.UserID2 = followingUser.UserID)
    WHERE fu.UserID1 = 1;

I changed Username to UserId because it made much more sense.

Edit:

Here's sqlfiddle i created to test it and it works.

Sign up to request clarification or add additional context in comments.

1 Comment

Yes, the 'WHERE' clause can be deleted, but I think it doesn't have anything to do with the error I'm still getting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.