Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Can you please help with a subquery?

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
Rao_S
Asked:
Rao_S
  • 3
  • 2
2 Solutions
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Rao_SAuthor Commented:
great!!!! thank you so much...it works!
0
 
sdstuberCommented:
glad I could help.

If you don't need anything else, please remember to close the question
0
 
Rao_SAuthor Commented:
thank you...!!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now