Clever Geek Handbook

Comparison Using Subqueries

This is the question I'm trying to answer:

What I have:

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 not correct because if acnum (academic number) 218 ​​runs in the same department 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 number of field interests.

interests table has fieldnum, acnum

The academic table has acnum, deptnum, name

the partition 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 contain a list of all academicians. but to make it a little 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

+3


source to share


4 answers


Unconfirmed 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)
+1


source


Perhaps using JOIN

can 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);
0


source


As I understand it, 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;
0


source


Use a common table expression (subquery) to get academics, their departments, and a count of their interests. Then query it twice to get the desired result.

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 when the logic sounds, the syntax can be awkward;)

0


source






More articles:


All Articles