inner query issue

Question

I have a doubt regarding an inner query

Schema:

DEPARTMENT (deptnum, descrip, instname, deptname, state, postcode)
ACADEMIC (acnum, deptnum*, famname, givename, initials, title)
PAPER (panum, title)
AUTHOR (panum, acnum**)
FIELD (fieldnum, id, title)
INTEREST (fieldnum, acnum**, descrip)

I have the output in this format :

select 
    acnum, title, givename, famname
from  
    academic a 
where 
    a.acnum in (select count(*) as no_of_papers, acnum 
                from author auth 
                join paper p on auth.panum = p.panum
                group by acnum 
                having count(*) < 20)
union
select 
    acnum, title, givename, famname
from 
    academic a 
where 
    a.acnum not in (select count(*) as no_of_papers, acnum 
                    from author auth 
                    join paper p on auth.panum = p.panum
                    group by acnum);

However along with the fields in the select statement of the outer queries, I also want the count(*) as no_of_papers in the result set.

I have been breaking my head for quite some time now.

Why not `join` to the inner query rather than using it in a `where` clause? — bouncyball, Sep 21 '16 at 14:37
Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Sep 21 '16 at 15:20

Vignesh Kumar A · Answer 1 · 2016-09-21T14:48:14.540

0

Use Join

Try Like this

SELECT acnum,title,givename,famname,no_of_papers
FROM academic a JOIN
( 
    SELECT COUNT(*) AS no_of_papers,acnum 
    FROM author auth JOIN paper p ON auth.panum=p.panum
    GROUP BY acnum HAVING COUNT(*)<20

)x ON 1 = 1
WHERE a.acnum IN (x.acnum)

UNION 

SELECT acnum,title,givename,famname,no_of_papers
FROM academic a 
(
    SELECT COUNT(*) AS no_of_papers,acnum 
    FROM author auth JOIN paper p ON auth.panum=p.panum
    GROUP BY acnum

 )y ON 1=1
WHERE a.acnum NOT IN (y.acnum)

edited Sep 21 '16 at 14:48

answered Sep 21 '16 at 14:42

Vignesh Kumar A

27,863
13
63
115

Why this "obfuscated" join and not `join (...) x on a.acnum = x.acnum`? – Sep 21 '16 at 14:59

ebyrob · Answer 2 · 2016-09-21T16:29:08.023

0

I don't think you need a union:

select 
  a.acnum,
  MIN(a.title),  -- only 1 value anyways if acnum is a primary key
  MIN(a.givename),
  MIN(a.famname),
  count(p.panum) as no_of_papers
from academic a
left join author auth on a.acnum = auth.acnum
left join paper p on auth.panum = p.panum
group by a.acnum
having 1 = 1 -- ??? no_of_papers < 20 ???
;

edited Sep 21 '16 at 16:29

answered Sep 21 '16 at 15:00

ebyrob

667
7
24

Group by should include all columns same as select column list right ? – Jaydip Jadhav Sep 21 '16 at 15:01
@JaydipJ Questioner's group by was always on `acnum` which seems to be a unique key of the academics table. – ebyrob Sep 21 '16 at 15:05
@ebyrob It will error out if you don't include all non-aggregate columns. You need to `GROUP BY a.acnum, a.title, a.givename, a.famname` – Eric Sep 21 '16 at 15:12
@ebyrob-I have used union so as to include the result for acnum that have not written any paper eg acnum not in (those acnum that have written a paper) – PandyaG Sep 21 '16 at 15:26
@Eric that really depends on the type of SQL database and options specified. (but I added `MIN()` around those values just in case) – ebyrob Sep 21 '16 at 16:29
@PandyaG a `LEFT JOIN` will also include those missing rows. – ebyrob Sep 21 '16 at 16:30

inner query issue

2 Answers2