so i have this tables with their own attributes
BOOK TABLE BOOKSHELF TABLE STORE TABLE LOCATION TABLE
id_book id_bookshelf id_store id_location
name name_bookshelf name_store name_location
author id_store id_location
id_bookshelf
And i wanna show this kind of table
TABLE_EXAMPLE
id_store name_store location no_books
1 store1 somewhere 5
2 store2 somewhere 0
And i did this script but it only show first row, i don't know how to show the store even if it doesn't has any book... Can someone help me? Here is de sql on MYSQL
SELECT a.id_store, a.name_store, b.name_location, COUNT(d.id_book) AS no_books
FROM STORE a
JOIN LOCATION b ON a.id_location = b.id_location
JOIN BOOKSHELF c ON a.id_store = c.id_store
LEFT JOIN BOOK d ON d.id_bookshelf = c.id_bookshelf
GROUP BY a.id_store;
CodePudding user response：
SELECT store.id_store,store.name_store,location.name_location,(SELECT COUNT(*) FROM bookshelf WHERE bookshelf.id_store = store.id_store) as no_books FROM `store` LEFT JOIN location ON store.id_location = location.id_location;
CodePudding user response：
You do not need to group the d.id_book. If you group by book then it will consider each book separately.
SELECT a.id_store, a.name_store, b.name_location, COUNT(d.id_book) AS no_books
FROM STORE a
JOIN LOCATION b ON a.id_location = b.id_location
JOIN BOOKSHELF c ON a.id_store = c.id_store
JOIN BOOK d ON d.id_bookshelf = c.id_bookshelf
GROUP BY a.id_store, a.name_store, b.name_location;
check the fiddle link below,