How to check different mysql tables and get output

May 28, 2022

I have retrieved following data from allbook table,

Book Name   copies
Book1         5
Book2         6
Book3         7
Book4         8

Further I have another table,issuedbooks which I issued those books to some students.

Student   BookName      Issued Date
Ann         Book1        5/20/2022
Mike        Book1        5/21/2022
Micheal     Book2        5/22/2022
John        Book3        5/23/2022

Now I need to consider both two tables and get the availability at the moment as below.

BookName    AvailableCopies
Book1            3
Book2            5
Book3            6
Book4            8

Are there any method to retrieve it from mysql query? can someone show me how to combine both above tables?

>Solution :

We can try to use subquery get all count from issuedbooks then do OUTER JOIN base on allbook which will keep BookName if only exists from allbook

SELECT ab.BookName,
       ab.copies - coalesce(ib.cnt,0) AvailableCopies
FROM allbook ab
LEFT JOIN (
    SELECT BookName,COUNT(*) cnt
    FROM issuedbooks
    GROUP BY BookName
) ib
ON ab.BookName = ib.BookName

sqlfiddle