Solved

Can you please help with a subquery?

Posted on 2013-01-17
6
428 Views
Last Modified: 2013-01-17
hi, i am trying to do a subquery or corelated query and i cannot get the right output.. can you please help?
i have attached a file with script and the desired output...
Subquery-Data.txt
0
Comment
Question by:Rao_S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38787165
SELECT   (SELECT LAST
            FROM staff s
           WHERE s.id = p.leadid)
             LEAD,
         (SELECT LAST
            FROM staff s
           WHERE s.id = c1.cioid)
             cio,
         COUNT(*)
    FROM proj p, cproj c1
   WHERE     p.projid = c1.projid
GROUP BY leadid, cioid
ORDER BY leadid;



I removed the "exists" clause because it doesn't really do anything except add work.  The inner join condition already forces mutual existence
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38787198
alternates using joins instead of scalar subqueries


SELECT LEAD, cio, COUNT
  FROM (SELECT   p.leadid, sp.LAST LEAD, sc.LAST cio, COUNT(*) COUNT
            FROM proj p, cproj c, staff sp, staff sc
           WHERE p.projid = c.projid AND sp.id(+) = p.leadid AND sc.id(+) = c.cioid
        GROUP BY p.leadid, sp.LAST, sc.LAST
        ORDER BY p.leadid);

SELECT LEAD, cio, COUNT
  FROM (SELECT   p.leadid, sp.LAST LEAD, sc.LAST cio, COUNT(*) COUNT
            FROM proj p
                 INNER JOIN cproj c ON p.projid = c.projid
                 INNER JOIN staff sp ON sp.id = p.leadid
                 LEFT OUTER JOIN staff sc ON sc.id = c.cioid
        GROUP BY p.leadid, sp.LAST, sc.LAST
        ORDER BY p.leadid);
0
 

Author Comment

by:Rao_S
ID: 38787214
great!!!! thank you so much...it works!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38787267
glad I could help.

If you don't need anything else, please remember to close the question
0
 

Author Closing Comment

by:Rao_S
ID: 38787636
thank you...!!!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question