SQL plus two tables select method

Question

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 'EMAIL1@GRE.AC.UK'.

OldProgrammer · Answer 1 · 2015-03-10T15:45:58.657

0

select * 
from avatars
where id in ( select id from users where email = 'EMAIL1@GRE.AC.UK');

or

SELECT   avatars.*
FROM avatars 
INNER JOIN users
ON avatars.id = users.id
and users.email = 'EMAIL1@GRE.AC.UK'

edited Mar 10 '15 at 15:45

answered Mar 10 '15 at 15:37

OldProgrammer

12,050
4
24
45

thanks it worked but only if the user has one avatar, i realize the CONSTRAINT is limiting my ability with the IDs, but i was wondering how could one ID hold multiple values, e.g. id 0001 could have 'avatar1', '13-JUNE-07', 2, 'MALE', 234); and 'avatar2', '13-JUNE-08', 2, 'MALE', 234); so on and so forth. – Sultan Ahmed Mar 10 '15 at 15:47

score 0 · Accepted Answer · answered Mar 11 '15 at 05:00

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.

SQL plus two tables select method

2 Answers2