Question 113474 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL SELECT Count() but with FULLJOIN

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;
Tags:

2

Answers


  1. 0 
    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;

    enter image description here

    Login or Signup to reply.
  2. 0

    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,

    https://www.db-fiddle.com/f/4FPtVroBeh16TvgXF3QVfJ/0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search