0

Brief: I'm making a hostel management system. I have the following 4 tables named as 'users' having users info, 'rooms' table having rooms' info, 'reservation' having info about rooms reserved by any user, and fee table having roomid, userid that has reserved the room. Fee is generated when the reservation request is confirmed.

Problem Statement: I want to select all users' info who have paid or not fee of the current month and also the name of the room that has been reserved by the user. Keep in mind that reservation status should be confirmed I have used this query: SELECT u.* FROM users u WHERE u.userid NOT IN ( SELECT f.user_id FROM fee f WHERE month(f.feemonth) = EXTRACT(month FROM (NOW())) ) but it doesn't show me the room name and fee info such as fee month, fee status paid fee. If you think I should change the strategy please suggest it. Tables structure is given as: users table rooms table reservation table fee table

Dharman
  • 26,923
  • 21
  • 73
  • 125
Qadeer Abbas
  • 25
  • 4
  • First thing is you should always include your database table structures as code (use dbfiddle) and not images. Now, instead of `EXTRACT(month FROM (NOW()))` you can simply achieve by `MONTH(NOW())`. And so in your original statement, to fetch more information from other tables with the user table, look into [JOIN operations](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join/6188334#6188334) – slashroot Nov 13 '21 at 10:58

0 Answers0