How to view data on single column

I'm trying to create a view of a table where multiple tables containing related data are combined for a much more readable table compared to 5 separate tables.

All of these tables that are being joined contain the booking reference as a foreign key.

Most of these tables contain multiple entries regarding to a single booking reference.

e.g. there are multiple cats for some bookings

Here is the view statement I have created:

USE CATTERY;
CREATE VIEW ALLBOOKING
AS
SELECT BOOK.Ref, BOOK.Food, BOOK.Interact, BOOK.Litter, BOOK.Start_Date, BOOK.End_Date, BOOK.Deposit, BOOK.Cost,
    GROUP_CONCAT( DISTINCT BOOKOWNER.ID) AS `Owner`,
    GROUP_CONCAT( DISTINCT BOOKCAT.Chip_ID) AS Cats,
    GROUP_CONCAT( DISTINCT BOOKOBS.ID) AS Observations,
    GROUP_CONCAT( DISTINCT BOOKROOM.ID) AS Room
FROM BOOK 
INNER JOIN BOOKOWNER ON BOOK.Ref = BOOKOWNER.Ref 
INNER JOIN BOOKCAT ON OOK.Ref = BOOKCAT.Ref
INNER JOIN BOOKOBS ON BOOK.Ref = BOOKOBS.Ref 
INNER JOIN BOOKROOM ON BOOK.Ref = BOOKROOM.Ref 

GROUP BY BOOK.Ref, BOOKOWNER.ID, BOOKCAT.Chip_ID, BOOKOBS.ID, BOOKROOM.ID

and here is the resulting table:

(ref)(food)(interact)(litter)(start date)(end date)(deposit)(cost)(owner)(catid)(observations)(room) 
'1', '1', '1', '0', '2019-11-22', '2019-11-25', '5', '53', '1', '1', '1', '4'
'1', '1', '1', '0', '2019-11-22', '2019-11-25', '5', '53', '1', '1', '2', '4'
'1', '1', '1', '0', '2019-11-22', '2019-11-25', '5', '53', '1', '1', '3', '4'
'1', '1', '1', '0', '2019-11-22', '2019-11-25', '5', '53', '1', '1', '4', '4'
'2', '0', '0', '2', '2019-11-24', '2019-11-28', '10', '104', '1', '2', '5', '3'

As you can see, there are multiple entries regarding reference number 1, from what I am aware the format I followed should have unnormalised this view and show multiple sets of data within a single field.

Solution

Likely, that the problem is with your GROUP BY clause, whose columns do not match the non-aggregated columns in the SELECT clause. As a matter of fact, you even have aggregated columns in the GROUP BY clause: for example, BOOKOWNER.ID belongs to both one of the GROUP_CONCAT() expression and to the GROUP BY clause.

In most databases, this would result in an error (and in MySQL as well, if sql mode ONLY_FULL_GROUP_BY was enabled).

Consider changing this:

GROUP BY BOOK.Ref, BOOKOWNER.ID, BOOKCAT.Chip_ID, BOOKOBS.ID, BOOKROOM.ID

To:

GROUP BY 
    BOOK.Ref, 
    BOOK.Food, 
    BOOK.Interact, 
    BOOK.Litter, 
    BOOK.Start_Date, 
    BOOK.End_Date, 
    BOOK.Deposit, 
    BOOK.Cost