Write a select query that displays “id” if a different “id” has same “panum”

The question i need to answer:

“Display a list of academics that have collaborated with another academic on more than one paper. List individual pairs of academics on each line. List only their academic numbers. Do not list duplicate pairs. (e.g 56,113 and 113,56 are duplicate pairs)”

So far iv been looking a way to write a query that displays duplicate “panum”(paper number) but i cant find anything online, not really sure what to search for to be honest

Some useful information:

Relation Schema:
ACADEMIC( acnum , deptnum*, famname, givename, initials, title)
AUTHOR( panum*, acnum* )
PAPER( panum , title)

Sample Data:
ACADEMIC values (221, 116, 'Korman', 'Robert', 'R J', 'Dr');
ACADEMIC values (222, 119, 'Morse', 'Frank', 'F J', 'Prof');
ACADEMIC values (223, 119, 'Bierling', 'Ken', 'K R E', 'Mr');
ACADEMIC values (224, 119, 'Ganguly', 'Ingo', 'I D R', 'Mr');
ACADEMIC values (225, 119, 'McAleer', 'David', 'D J G', null);

AUTHOR values (3001, 221);
AUTHOR values (3004, 222);
AUTHOR values (3001, 223);
AUTHOR values (3005, 224);
AUTHOR values (3004, 225);

PAPER values (3001, 'Speedup in parallel algorithms for adaptive quadrature');
PAPER values (3002, 'Odd-even reduction for banded linear equations');
PAPER values (3003, 'Estimating the mean of a correlated binary sequence');
PAPER values (3004, 'Optimality of the Fast Fourier transform');
PAPER values (3005, 'Time bounded random access machines with parallel processing');

if we use the sample data as example. it should output the acnum of those that have worked on the same paper together.

Answer

You need a self join of the table AUTHOR, GROUP BY the pair of academics from each copy of the table and set the condition in the HAVING clause:

select a1.acnum Academic1, a2.acnum Academic2
from author a1 inner join author a2
on a2.panum = a1.panum and a1.acnum < a2.acnum
group by a1.acnum, a2.acnum
having count(*) > 1

Advertisement

Answer