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)?
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)
Another approach -- if you want more columns about the academic -- is
select a.* from academics a where not exists (select 1 from interest i where a.acnum = i.acnum and upper(descrip) like '%DATA%' );