Search code examples
sqlselectsqlpluscreate-table

SQL plus two tables select method

This is what I have done in SQL plus. So far I'm very new to this:

CREATE TABLE u
();

CREATE TABLE a
();

Essentially a user can have one or more avatars linked to its email address, but I want to know how I can link the avatars from the second table and assign it to the email address on the first table, in such a way that if I use the SELECT function that I can list all the avatars with an email address of '[email protected]'.

Solution

  • If, as it appears, the ID field of both tables should contain the same value for the same user, then you have set up a 1-1 relationship. Each user can have one and only one avatar. If you want more than one avatar, then it has to have an independent ID and another UserID to refer to the user.

    create table Avatar(
    ID     NUMBER CONSTRAINT pk_email PRIMARY KEY,
    UserID number, 
    name   VARCHAR2(32),
    dateOfBirth DATE,
    strength NUMBER(9),
    gender VARCHAR2(7),
    hoard  INTEGER,
    constraint FK_Avatar_User foreign key( UserID )
        references User( ID )
);
create index IX_Avatar_User on Avatar( UserID, ID );

    Now each avatar will have it's own ID value. Each avatar can only be assigned to one user, but the same user ID can be entered for many avatars. The index speeds up access.

    Then to show the avatar(s) assigned to a user:

    select  U.ID UserID, U.email, A.ID AvatarID, A.Name AvatarName
from    Users U
join    Avatar A
    on  A.UserID = U.ID
where   U.ID = 123;  -- If you are only interested in one particular user.

    Btw, unless you are working for the nursery of a hospital, you will find that DateOfBirth rarely has a default defined for it. If that detail isn't known when the record is created, NULL is much preferred over TODAY.