Home » SQL & PL/SQL » SQL & PL/SQL » Record display problem (SQL*Plus: Release 10.1.0.2.0 )
Record display problem [message #434019] Sun, 06 December 2009 13:35 Go to next message
Raakh
Messages: 12
Registered: December 2009
Junior Member
SQL> ed
Wrote file afiedt.buf

  1  select emp_id,
  2              (select count(*) from empEdu where emp_id=a.emp_id) as empEdu,
  3              (select count(*) from Experience where emp_id=a.emp_id)  as Experience,
  4              (select count(*) from Expertise where emp_id=a.emp_id)  as Expertise,
  5              (select count(*) from postCV where emp_id=a.emp_id)  as postCV
  6* from employee a
SQL> /

    EMP_ID     EMPEDU EXPERIENCE  EXPERTISE     POSTCV
---------- ---------- ---------- ---------- ----------
         1          2          1          1          1
        21          1          2          0          1

SQL> 

There is one parent table with the name of "Employee" and 4 child tables i.e. "empEdu","Experience","Expertise","postCV"

In above example CV containing emp_id=1 should be displayed and emp_id=21 should not be displayed as its one child table expertise has no record

QUESTION
=========
I want to display emp_id by selecting those emp_ids that contains data in four child tables. If any of the child table doesn't contain data then it'll not be displayed. In above example SQL statement displays all emp_ids and tables where as I need output like this:

   EMP_ID    
---------- 
         1        /**emp_id=1 all tables contain records so this is displayed**/
		  /**emp_id=21 missing record for one table expertise then its not selected**/ 
       


Thanks in anticipation
Re: Record display problem [message #434021 is a reply to message #434019] Sun, 06 December 2009 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
WHERE EXISTS .....
AND EXISTS ....
....
Re: Record display problem [message #434024 is a reply to message #434019] Sun, 06 December 2009 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  c1 as (select emp_id, count(*) nb from t1 group by emp_id),
  c2 as (select emp_id, count(*) nb from t2 group by emp_id),
  c3 as (select emp_id, count(*) nb from t3 group by emp_id),
  c4 as (select emp_id, count(*) nb from t4 group by emp_id)
select emp_id, c1.nb, c2.nb, C3.nb, c4.nb
from emp, c1, c2, c3, C4
where c1.emp_id = emp.emp_id
  and c2.emp_id = emp.emp_id
  and c3.emp_id = emp.emp_id
  and c4.emp_id = emp.emp_id
/

Regards
Michel

Re: Record display problem [message #434026 is a reply to message #434024] Sun, 06 December 2009 13:58 Go to previous message
Raakh
Messages: 12
Registered: December 2009
Junior Member
Thanks from the bottom of my heart

Best Regards
Previous Topic: union
Next Topic: display message
Goto Forum:
  


Current Time: Sat Dec 03 08:17:37 CST 2016

Total time taken to generate the page: 0.06399 seconds