CODEWITHSUNDEEP
mysqlsql
lopmkipm
lopmkipm

Reputation: 77

Group by one field and display multiple fields (MySQL)

So i have these three tables :

Booking :

+----+---------+--------+---------------+-------------+
| id | idRoom  | idUser | startDateTime | endDateTime |
+----+---------+--------+---------------+-------------+
|  4 |       3 |      1 | 07/06/2020    | 07/07/2020  |
|  5 |      3  |     2  | 07/06/2021    | 07/06/2021  |
+----+---------+--------+---------------+-------------+

Room : 

+----+--------------+
| id | description  |
+----+--------------+
|  3 | Room 1       |
+----+--------------+

User :

+----+----------+
| id | userName |
+----+----------+
|  1 | User 1   |
|  2 | User 2   |
+----+----------+

And want to select all the bookings (listed in table one) while displaying the User and the Room fields infos and group by the Room object. I am using the JOIN clause along with the GROUP BY clause as follows : 

select distinct r, b, u 
from Booking b 
join Room r on b.idRoom=r.id 
join User u on b.idUser=u.id 
where r.id=3
group by r, b, u 
order by r

But it is not rendering the desired result. Anyone suggests a working SQL query ?

EDIT (Desired Result ) : 

+-------+--------+-----------+
| Rooms  | Users  | Bookings  |
+-------+--------+-----------+
|     3 |      1 |         4 |
|       |      2 |         5 |
+-------+--------+-----------+

Upvotes: 0

Views: 271

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

As you wants to group by your query output by room, you can start your query from the room table. But not sure how you can get the Json formatted output. You can achieve the following output and rest part you should manage in the frontend.

Possible output-

Rooms  Users  Bookings
3      1      4
3      2      5

Query for the above output -

SELECT R.id AS Rooms,
B.idUser AS Users,
B.ID AS Bookings
FROM Room R
INNER JOIN Booking B ON R.Id = B.idRoom

For more details of a User, you can join the User table Now.

Upvotes: 1

Related Questions