CODEWITHSUNDEEP
mysqlsqlsqlite
Huseyin Yesiler
Huseyin Yesiler

Reputation: 85

Writing a select query using 2 tables and 2 conditions

Im having trouble writing the logic to see if an id from one table appears 12 or more times in another table.

The question is asking: "There are some academics that have written more than 12 papers and there are some academics are interested in fields that have the word “database” in the title. List the academic number of each academic that meet either or both of these conditions."

this is what iv written so far

SELECT acnum 
FROM academic 
WHERE acnum IN (
SELECT panum 
FROM paper 
WHERE title LIKE '%database%');

Also some information about the database relation/tables.

scheme of the tables possible needed:

AUTHOR( panum*, acnum* )
PAPER( panum , title)

Sample Data:

PAPER values(100, 'Intro to programming');
PAPER values(101, 'Intro to database systems');
PAPER values(102, 'Intro to database management');
PAPER values(103, 'Intro to computing');
PAPER values(104, 'Intro to database analysis');

AUTHOR values (101, 25);
AUTHOR values (102, 25);
AUTHOR values (103, 25);
AUTHOR values (104, 25);
AUTHOR values (105, 25);
AUTHOR values (106, 25);
AUTHOR values (107, 25);
... up until 12 that all have same acnum (25).

Upvotes: 0

Views: 97

Answers (1)

forpas
forpas

Reputation: 164099

You have to join 5 tables and set the conditions in the HAVING clause:

select ac.acnum 
from academic ac
left join author a on a.acnum = ac.acnum
left join paper p on p.panum = a.panum and p.title like '%database%'
left join interest i on i.acnum = ac.acnum
left join field f on f.fieldnum = i.fieldnum and f.title like '%database%'
group by ac.acnum
having 
  count(distinct p.panum) > 12
  or
  sum(f.title is not null) > 0

If the 1st condition is not about the academics that have written at least 12 papers about 'database' but any paper, then remove the condition:

and p.title like '%database%'

Upvotes: 1

Related Questions