CODEWITHSUNDEEP
sqloracle-database
Gary
Gary

Reputation: 67

SQL Query for PL/SQL statement won't work

I am trying to execute an SQL statement that I am planning on using with a PL/SQL cursor down the road. It will fetch an employees name and the projects they are working on if they are working on more than 1 project. For some reason, the "having count(pno)>1" stipulation will not work here. It just says "no data found"

Is there anything I'm doing wrong? I included my DB code below the query.

Query:

select pname, fname
from project, works_on, employee
where pno=pnumber and essn=ssn
group by pname, fname
having count(pno)>1;

Works_on table:

create table works_on (
Essn    char(9) not null,
Pno int not null,
hours   decimal(3,1),
primary key(essn, pno),
foreign key(Essn) references employee,
foreign key(pno) references project);

Project table:

create table project (
Pname varchar2(15) not null,
Pnumber int     not null,
Plocation varchar2(15),
Dnum    int not null,
primary key (Pnumber),
unique (Pname),
foreign key(Dnum) references department(Dnumber));

Employee table:

 create table employee (
 Fname  varchar2(15) not null,
 Minit  char(1),
 Lname  varchar2(15) not null,
 Ssn    char(9),
 Bdate  date,
 Address    varchar2(30),
 Sex    char(1),
 Salary decimal(10,2),
 super_ssn  char(9),
 dno    int,
 primary key (Ssn),
 foreign key (dno) references department(Dnumber));

EDIT

I managed to make this work instead:

select fname, pname
  from employee, works_on, project
  where essn=ssn and pno=pnumber
  group by fname, pname
  having count(pnumber) > 1

What made pnumber work in place of pno?

Upvotes: 0

Views: 131

Answers (1)

MT0
MT0

Reputation: 167867

My expected output is a list of Employee First Names and Project names where the employee is working on more than 1 project.

Something like this:

SELECT e.fname, p.projects
FROM   (
  SELECT w.essn,
         LISTAGG( p.pname, ',' ) WITHIN GROUP ( ORDER BY p.pname ) AS projects
  FROM   works_on w
         INNER JOIN
         project p
         ON ( w.pno = p.pnumber )
  GROUP BY w.essn
  HAVING COUNT( DISTINCT w.pno ) > 1
) p
INNER JOIN
employee e
ON ( p.essn = e.ssn )

or:

SELECT e.fname,
       p.pname
FROM   (
         SELECT w.*,
                COUNT( pno ) OVER ( PARTITION BY essn ) AS num_projects
         FROM   works_on w
       ) w
       INNER JOIN
       employee e
       ON ( e.ssn = w.essn )
       INNER JOIN
       project p
       ON ( w.pno = p.pnumber )
WHERE  w.num_projects > 1

Upvotes: 1

Related Questions