CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle-sqldeveloper
Remus Case
Remus Case

Reputation: 470

How to get row having null value in a query when relating two or more table together?

drop table person;
drop table interest;
drop table relation;


create table person (
      pid int primary key,
      fname varchar2(20),
      age int,
      interest int references interest(intID),
      relation int references relation(relID)
);

create table interest (
      intID int primary key,
      intName VARCHAR2(20)
);

create table relation (
      relID int primary key,
      relName varchar2(20)
);

insert into person values(1, 'Rahul', 18, null, 1);
insert into person values(2, 'Sanjay', 19, 2, null);
insert into person values(3, 'Ramesh', 20, 4, 5);
insert into person values(4, 'Ajay', 17, 3, 4);
insert into person values(5, 'Edward', 18, 1, 2);

insert into interest values(1, 'Cricket');
insert into interest values(2, 'Football');
insert into interest values(3, 'Food');
insert into interest values(4, 'Books');
insert into interest values(5, 'PCGames');

insert into relation values(1, 'Friend');
insert into relation values(2, 'Friend');
insert into relation values(3, 'Sister');
insert into relation values(4, 'Mom');
insert into relation values(5, 'Dad');

select * from person;
select * from interest;
select * from relation;

The above code shows the table and its schema in code. Below is the query that I have written.

select person.fname, interest.intName, relation.relName
from person, interest, relation
where person.interest = interest.intID and relation.relID = person.relation;

This is the output I am getting. (Img1)

Instead of output I am getting, this is the output I need. (Img2)

The img2 is written by me, and in my query (as in img1) the null values are being ignored.

Why is this happening? And Please do provide solution. Thank You.

Upvotes: 3

Views: 181

Answers (1)

Littlefoot
Littlefoot

Reputation: 142958

Looks like outer join is what you need.

SQL> select p.fname, i.intname, r.relname
  2  from person p left join interest i on p.interest = i.intid
  3                left join relation r on r.relid = p.relation;

FNAME                INTNAME              RELNAME
-------------------- -------------------- --------------------
Rahul                                     Friend
Edward               Cricket              Friend
Ajay                 Food                 Mom
Ramesh               Books                Dad
Sanjay               Football

SQL>

Upvotes: 1

Related Questions