CODEWITHSUNDEEP
sqloracle-database
Siti
Siti

Reputation: 163

Sql query with multiple condition but some of item is not available in certain table

I am facing problem with sql query when I try to join this tree table and get the data. I am using Oracle database.

Personal

id_no   name
-------------    
0001    John
0002    Peter
0003    Mike

position
id_no  name
-------------
0001  programmer
0002  Engineer
0003  Clerk

extra_skill
employee_id    skill
--------------------------
0001         Visual Studio 2008
0003         Crystal Report

Requirement: Display details of employees:

Example:

Employee No : 0001
Employee Name : John
Employee Position : Programmer
Employee Skill : Visual Studio 2008

My sql statement is 

SELECT a.id_no, a.name, b.name, c.skill
  FROM personal a, POSITION b, extra_skill c
 WHERE a.id_no = b.id_no
   AND b.id_no = c.employee_id
   AND c.employee_id = "USER INPUT";

The problem is when 

SELECT a.id_no, a.name, b.name, c.skill
  FROM personal a, POSITION b, extra_skill c
 WHERE a.id_no = b.id_no
   AND b.id_no = c.employee_id
   AND c.employee_id = "0002";

This query give me NULL because in the table extra_skill does not have 0002.

I want it possible to get data even in the third table no value.

Expected result:
  Employee No : 0002
  Employee Name : Peter
  Employee Position : Engineer
  Employee Skill :

How can I implement such a query?

Your attentions and helps are much appreciated. Thank you, Siti..:)

Upvotes: 1

Views: 4490

Answers (2)

APC
APC

Reputation: 146219

The way to do this is with outer joins. I have used the ANSI joining syntax (introduced in Oracle 9i) for this example, because it is clearer and also Oracle now recommend using it.

SELECT a.id_no, a.name, b.name as position, c.skill 
  FROM personal a INNER JOIN position b
        on (a.id_no = b.id_no )
      LEFT OUTER JOIN extra_skill c 
       on ( a.id_no = c.employee_id )
WHERE a.id_no = '0002';

Note that I have changed the filter condition to select on PERSONAL.ID. If you attempt to use c.employee_id = '0002' (as you do in your question) you will get no rows returned.

You could include EXTRA_SKILLS.EMPLOYEE_ID in the join condition rather than the WHERE clause. 

SELECT a.id_no, a.name, b.name as position, c.skill  
FROM personal a INNER JOIN position b 
    on (a.id_no = b.id_no ) 
  LEFT OUTER JOIN extra_skill c  
   on ( a.id_no = c.employee_id  
        and c.employee_id = '0002');

Generally including filters in the join section is not considered good practice, because it can effect the result set in ways we're not expecting. In your case it will return every row in PERSONAL and POSITION and no values from EXTRA_SKILL. This is probably not what you want to happen. Here is a SQL Fiddle to prove it.

Upvotes: 3

Corinne Kubler
Corinne Kubler

Reputation: 2092

You will have to use an outer join. The purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values. 

SELECT a.id_no, a.name, b.name, c.skill
  FROM personal a, POSITION b, extra_skill c
WHERE a.id_no = b.id_no
AND b.id_no = c.employee_id
AND c.employee_id(+) = "USER INPUT";

Upvotes: -1

Related Questions