CODEWITHSUNDEEP
mysql
Henz D'Wraith
Henz D'Wraith

Reputation: 93

Select book count while substracting from a joined table

I have the following table with all books:

All books

And another table to record borrowed books:

Borrowed books

My expected output should show the list of books with the current ammount (named jumlah_buku), calculated by substracting borrowed books:

Expected output

This is what I have tried:

SELECT BUKU.KODE_BUKU AS 'KODE BUKU',BUKU.JUDUL AS 'JUDUL',BUKU.NAMA_PENGARANG AS 'NAMA PENGARANG', (BUKU.JUMLAH_BUKU) - (SELECT COUNT(*) FROM (select KODE_BUKU from PEMINJAMAN)) AS 'JUMLAH BUKU' FROM BUKU INNER JOIN PEMINJAMAN ON BUKU.KODE_BUKU = PEMINJAMAN.KODE_BUKU

But it's not working.

Please help me.

Upvotes: 0

Views: 39

Answers (1)

Jerry Jeremiah
Jerry Jeremiah

Reputation: 9618

Try this correlated subquery:

SELECT BUKU.KODE_BUKU AS 'KODE BUKU',
BUKU.JUDUL AS 'JUDUL',
BUKU.NAMA_PENGARANG AS 'NAMA PENGARANG',
BUKU.JUMLAH_BUKU - (
    SELECT COUNT(KODE_BUKU)
    FROM PEMINJAMAN
    WHERE BUKU.KODE_BUKU = PEMINJAMAN.KODE_BUKU
) AS 'JUMLAH BUKU'
FROM BUKU;

Upvotes: 1

Related Questions