I have three tables:
First table is 'user':
user_id | Name |
----------------------
1234354 | Abu Bakar |
2543353 | Ali |
3342342 | Aliza |
4234243 | Ali |
5234324 | Mike |
Second table is 'meeting':
meeting_id | meeting_name | meeting_startDate | meeting_endDate | room_name |
----------------------------------------------------------------------------------
1 | Discussion 1 | 2021-05-29 | 2021-05-31 | room 1 |
2 | Discussion 2 | 2021-06-01 | 2021-06-02 | room 2 |
3 | Discussion 3 | 2021-05-02 | 2021-05-02 | room 1 |
4 | Discussion 4 | 2021-05-03 | 2021-05-03 | room 1 |
5 | Discussion 5 | 2021-05-04 | 2021-05-04 | room 1 |
Third table is 'booking':
book_id | meeting_id | user_id |
-------------------------------------
0001 | 1 | 1234354 |
0002 | 2 | 1234354 |
0003 | 3 | 1234354 |
0004 | 4 | 1234354 |
0005 | 5 | 1234354 |I want to allow per user can book each room_name up until 5 times for per month.
(Example: Abu Bakar can book room 1 only for 5 times per month). However, Abu Bakar should not able to booked room 1 for more than 5 times per month.
How can I write my query for above situation because I need to prepare SQL query for above so that it can help me to perform in my php if else statement?
Can someone assist me on this?
Note: As I know i agree there is insert, select, inner join and count involve but I just dont understand how to do the calculation for the date, place, and limitation for a user ID