oracle joining multiple tables

Question

I have two tables, one which look something like this

Position(posNum, posTitle, posSalary)

sample data:

insert into position values ('P0009','Systems Engineers',5600);
insert into position values ('P0010','Senior Lecturer', 9000);
insert into position values ('P0011','Database Administrator',4500);

and another table like this

SKILL(skill,skillDesc)

sample data:

insert into skill values ('SK009','Database Optimization');
insert into skill values ('SK010','Oracle XE 11g');

which are referenced by a table like this

SKILLNEEDED(skillneededPosNum, skillneededSkill)

sample data:

insert into skillneeded values ('P0009','SK010',10);
insert into skillneeded values ('P0010','SK401',10);
insert into skillneeded values ('P0010','SK807',10);

is there any mistake in my query as it returns no rows found which should not be the case

SELECT p.posNum, p.posTitle, p.posOfferedBy
  FROM POSITION p
  JOIN SKILLNEEDED c
    ON p.posNum = c.skillneededPosNum
  JOIN SKILL s
    ON s.skill = c.skillneededSkill
 WHERE s.skillDesc = 'Oracle XE 11g' AND s.skillDesc = 'Database Design';

codeproject.com/Articles/435694/… first of all understand the concepts of joining from this link... and try to do it again... — The Hungry Dictator
– The Hungry Dictator, Commented Nov 1, 2013 at 3:21
@Rohan i've added some data, so I've read some resources online and they suggest to use full outer join is it true? — koi_s3ng
– koi_s3ng, Commented Nov 1, 2013 at 3:47
I was checking the data. Your last clause mentions skill 'Oracle XE 11g' & 'Database Design', but there is no record that has both these skill code assigned to them. Only P0009-Systems Engineer has skill SK010-Oracle XE 11g, but not both skills — Rohan
– Rohan, Commented Nov 1, 2013 at 3:59

duplode · Accepted Answer · 2013-11-01 03:20:23Z

2

I suppose there should be an OR instead of an AND in your WHERE clause.

answered Nov 1, 2013 at 3:20

duplode

34.5k7 gold badges88 silver badges160 bronze badges

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

Comments

APC · Accepted Answer · 2013-11-01 04:08:43Z

Your initial query fails because the entire WHERE is applied to each row. Clearly it cannot be true that a=1 AND a=2 so your query returns zero rows.

This solution is not necessarily the most elegant but I think it illustrates the principle. It uses the WITH clause to make a sub-query which can be referenced multiple times in the main query.

with skillz as
   ( select p.posNum, s.skillDesc
     from SKILLNEEDED c
          join SKILL s
              ON s.skill = c.skillneededSkill
          join POSITION p
              ON p.posNum = c.skillneededPosNum )
select p.*
from POSITION p
     join skillz s1 
          on s1.posNum = p.posNum 
     join skillz s2 
          on s2.posNum = p.posNum 
WHERE s1.skillDesc = 'Oracle XE 11g' 
AND s2.skillDesc = 'Database Design';

Collectives™ on Stack Overflow

oracle joining multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related