CODEWITHSUNDEEP
sqloracle-database
Elio
Elio

Reputation: 73

How to select a member that have borrowed all books

The question is:

"For a given theme,list the members name and birthdate who have borrowed all the books of this theme."

I have tried this:

SELECT 
    t.label, m.name, m.tel
FROM 
    Theme t, Member m, Book b, Copies c, Borrow b1
WHERE 
    t.themecode = b.themecode 
    AND b.bookid = c.bookid 
    AND b1.copyid = c.copyid  
    AND b1.mid = m.mid 
    AND t.label = 'Action'

and it returned all members who borrowed this book of the theme, but I want only members who borrowed ALL books of this theme

Upvotes: 0

Views: 1539

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can consider HAVING clause with COUNT aggregation combining tables through LEFT JOIN :

SELECT t.label,m.name,m.tel
  FROM Theme t
  LEFT JOIN Book b ON t.themecode=b.themecode 
   AND t.label='Action' 
  LEFT JOIN Copies c ON b.bookid=c.bookid
  LEFT JOIN Borrow b1 ON b1.copyid=c.copyid  
  LEFT JOIN Member m ON b1.mid=m.mid
 GROUP BY t.label,m.name,m.tel
 HAVING count(distinct b.bookid)= count(t.themecode)

Upvotes: 0

EoinS
EoinS

Reputation: 5482

You have a granular list of Members and all Borrows they have had for a certain theme.

You want to see how many distinct Books a member has borrowed.

You also want to see how many total distinct books there are for a given theme.

You only want to list the members that have borrowed as many distinct books as there are total books for a theme.

SELECT m.name,m.tel
FROM Theme t,Member m, Book b,Copies c,Borrow b1
WHERE t.themecode=b.themecode AND 
b.bookid=c.bookid AND
b1.copyid=c.copyid AND
b1.mid=m.mid AND 
t.label='Action' 
Group by m.name, m.tel-- distinct users
Having count(distinct bookid) = -- number of distinct books each person borrowed
(Select count(distinct bookid) from Theme t, Book b WHERE t.themecode=b.themecode and t.label= 'Action') --total number of distinct books for your Theme

Also comments/answers are right you should use explicit joins I'm just helping you add the logic to your script and explain why it works.

Upvotes: 1

forpas
forpas

Reputation: 164064

Use a CTE to get the number of books with theme 'Action'.
Join all the tables properly, group by member and count the number of distinct books they have borrowed and compare it with the result of the CTE:

with cte as(
  select count(*) counter from book where themecode = (
    select themecode from theme where label = 'Action'  
  )
)
select t.label, m.name, m.birthdate
from member m
inner join borrow br on br.mid = m.mid
inner join copies c on c.copyid = br.copyid
inner join book b on b.bookid = c.bookid
inner join theme t on t.themecode = b.themecode
where t.label = 'Action'
group by t.label, m.mid, m.name, m.birthdate
having count(distinct b.bookid) = (select counter from cte)

Upvotes: 0

Related Questions