CODEWITHSUNDEEP
mysqlsql
mdnba50
mdnba50

Reputation: 379

MySQL get amount of books for each user

I'm trying to return the amount of books a member has from all libraries.

In different libraries the member has a different member level. 

  SELECT  b.*
    FROM    library a
            LEFT JOIN member b on b.id=a.member_id
            LEFT JOIN member_level d on d.id=a.member_level_id
            LEFT JOIN book e on e.library_id=a.id
    WHERE   ((a.member_id='1' AND a.member_level_id='1') 
            OR (a.member_id='2' AND a.member_level_id='2')) AND a.id='1'

I've done the basic above to get members from library 1 and their membership level but i don't know how to get the total amount of books from all libraries?

e.g. Member 1 has 564 books from all libraries and is a gold member at Library 1, Member 2 has 365 books from all libraries and is a silver member at Library 1.

I want to order by member levels at library 1. That is why i included in where clause.

How do i change the query to accomplish this?

Upvotes: 0

Views: 155

Answers (1)

Mittal Patel
Mittal Patel

Reputation: 2762

You can achieve it as below:

SELECT  a.member_id, Sum(e.id) as books,  a.member_level_id, a.id as library_id
FROM    library a
        LEFT JOIN member b on b.id=a.member_id
        LEFT JOIN member_level d on d.id=a.member_level_id
        LEFT JOIN book e on e.library_id=a.id
WHERE a.member_id IN ('1', '2')
GROUP BY a.member_id, a.id,  a.member_level_id
Order by a.member_level_id

In your query, you might have a mistake in the join:

LEFT JOIN book e on e.id=a.library_id

I think it should be 

LEFT JOIN book e on e.library_id=a.id

Upvotes: 2

Related Questions