CODEWITHSUNDEEP
sqloracle-databasecomparesubquery
DFY
DFY

Reputation: 31

comparing using sub-queries

This is the question im trying to answer:

What i have is:

SELECT DISTINCT a1.acnum
FROM academic a1, academic a2
WHERE a1.deptnum = a2.deptnum
AND a1.acnum <> a2.acnum
AND a1.acnum IN (Select acnum
from interest
group by acnum
having count(acnum) >1);

which is wrong because what im doing is if acnum (academic number) 218 works in the same dept as acnum 217, AND has the same interests as acnum 199 (diff department) then i add acnum 218 to the list.

HOWEVER, I should only add acnum 218 and 217 if BOTH of them have the same amount of field interests.

interest table has fieldnum , acnum

Academic table has acnum , deptnum, name

department table has deptnum, deptName

 FIELDNUM           ACNUM DESCRIP                                                                        
------------------ -------------------- 
292                 100  Multiprocessor and Special purpose computer design                               
293                 100  General (HW)                                                                     
293                 197  Computer architecture

The output should only list all the academics' number.. but to make it a bit clear:

Acnum Deptnum Interest
1        1       g&f
2        1       g&f
3        2        f
4        3        l
5        4       r&l
6        4       r&l

The output should be: 1 2 5 6

Upvotes: 3

Views: 244

Answers (4)

APC
APC

Reputation: 146239

Use a common table expression (sub-query) to get the academics, their departments and a count of their interestes. Then query from it twice to get the required output.

with cte as ( select a.acnum
                     , a.deptnum
                     , count(i.acnum) as int_cnt
              from academic a
                   , interest i
              where i.acnum = a.acnum
              group by  a.acnum
                     , a.deptnum
            )
select ct1.acnum
       , cte1.deptnum
       , cte1.in_cnt
from cte cte1
     , cte cte2
where cte2.deptnum = cte1.deptnum 
and cte2.int_cnt = cte1.int_cnt  
and cte2.acnum != cte1.acnum 
order by cte1.deptnum
         , cte1.acnum

caveat - not actually tested, so while the logic is sound the syntax may be awry ;)

Upvotes: 0

Dale M
Dale M

Reputation: 2473

Untested but should be good

SELECT DISTINCT a1.acnum
FROM academic a1
INNER JOIN academic a2 ON a1.deptnum = a2.deptnum
                          AND 
                          a1.acnum <> a2.acnum
INNER JOIN interest i1 ON a1.acnum=i1.acnum
GROUP BY a1.acnum
HAVING COUNT(i1.acnum)=(SELECT COUNT(*)
                        FROM interest i2
                        WHERE i1.acnum=i2.acnum)

Upvotes: 1

Rahul Vasantrao Kamble
Rahul Vasantrao Kamble

Reputation: 251

As per my understanding it should work 

SELECT 
      listagg(a1.acnum,',') within group( order by a1.acnum) , a1.deptnum,a2.cnt
FROM 
     academic a1,
     (Select 
            acnum,count(*) as cnt
      from interest
      group by acnum
     ) a2
where 
     a1.acnum=a2.acnum
group by 
     a1.deptnum,a2.cnt
having count(*)>1;

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

Perhaps using JOIN could give you better results here:

SELECT DISTINCT a1.acnum
FROM academic a1
JOIN academic a2
ON a1.deptnum = a2.deptnum
AND a1.acnum <> a2.acnum
AND a1.acnum IN (Select acnum
from interest
group by acnum
having count(acnum) >1);

Upvotes: 0

Related Questions