How would a nested subquery look if its purpose is to count values between tables?-CodePudding

The tables [...] have the following relevant attributes:

RENTAL (member_id, title_id)

MEMBER (member_id, last_name, first_name)

The task is to count the number of times each member has borrowed a certain book. For example, a member with member_id 102 could have 2 entries in the rental table for the book with title_id 99. For each member, show the number of times he has borrowed each book.

Now, this is how my (terrible) idea went:

SELECT m.member_id, r.title_id, m.last_name, m.first_name, count(*)
FROM (SELECT rr.title_id, m.member_id
      FROM rental rr
      WHERE rr.title_id > = r.title_id and m.member_id = rr.title_id)   
FROM member m,
     rental r  
WHERE m.member_id = r.member_id

It doesn't work and I would appreciate if someone guided me through what I'm doing wrong

CodePudding user response：

Try it like this...

Select
    m.MEMBER_ID,
    m.LAST_NAME,
    m.FIRST_NAME,
    r.TITLE_ID,
    Count(r.TITLE_ID)  "BORROWED"
FROM
    MEMBER m
INNER JOIN 
    RENTAL r ON(r.MEMBER_ID = m.MEMBER_ID) 
GROUP BY
    m.MEMBER_ID,
    m.LAST_NAME,
    m.FIRST_NAME,
    r.TITLE_ID

If you join the tables you can still use aggregate functions, like Count(), and group the data by columns that are not aggregated. This should do the job. Regards...

CodePudding user response：

From Oracle 12, you can use a LATERAL join:

SELECT m.member_id, 
       m.last_name,
       m.first_name,
       r.num_rentals
FROM   member m
       CROSS JOIN LATERAL (
         SELECT title_id,
                COUNT(*) AS num_rentals
         FROM   rental r
         WHERE  m.member_id = r.member_id
         GROUP BY
                r.title_id
       ) r

or you could use a LEFT OUTER JOIN:

SELECT m.member_id, 
       m.last_name,
       m.first_name,
       r.title_id,
       COALESCE(r.num_rentals, 0) AS num_rentals
FROM   member m
       LEFT OUTER JOIN (
         SELECT member_id,
                title_id,
                COUNT(*) AS num_rentals
         FROM   rental
         GROUP BY
                member_id,
                title_id
       ) r
       ON (m.member_id = r.member_id)

or, moving the aggregation to the outer query:

SELECT m.member_id, 
       MAX(m.last_name) AS last_name,
       MAX(m.first_name) AS first_name,
       r.title_id,
       COUNT(r.title_id) AS num_rentals
FROM   member m
       LEFT OUTER JOIN rental r
       ON (m.member_id = r.member_id)
GROUP BY
       -- group by the primary key
       m.member_id,
       r.title_id

If you use an INNER JOIN then you will not get any results when a member has not borrowed any books.

fiddle