How to isolate query to find particular fields?

Here is the database I'm using: https://drive.google.com/file/d/1ArJekOQpal0JFIr1h3NXYcFVngnCNUxg/view?usp=sharing

I'm trying to create a query to output the academics (acnum) who are ONLY interested in "Data" (descrip) fields.

I tried this:

select acnum
from interest
where upper(descrip) like '%DATA%';

It partly works, it finds the academics(acnum) that are interested in fields(descrip) with 'Data' in their names, but it also lists the academics(acnum) that have other interests besides 'Data' fields, how do I find academics(acnum) that are interested ONLY in 'Data' fields(descrip)?

Thank you.

2 answers

  • answered 2018-05-16 06:09 M Khalid Junaid

    To get academics(acnum) that are interested ONLY in 'Data' fields(descrip) you could use following query with some aggregation to satisfy your criteria

    SELECT acnum
    FROM interest
    GROUP BY acnum
    HAVING COUNT(*) = SUM(CASE WHEN UPPER(descrip) LIKE '%DATA%' THEN 1 ELSE 0 END)
    

  • answered 2018-05-16 11:10 Gordon Linoff

    Another approach -- if you want more columns about the academic -- is not exists:

    select a.*
    from academics a
    where not exists (select 1
                      from interest i
                      where a.acnum = i.acnum and upper(descrip) like '%DATA%'
                     );