MYSQL database Help please

[BITCHLORD]

mga ka Symb patulong naman
po ako kung paano palabasin ung data


may table po kc ako booking table ung bookingtable po nandun ung room_no,date_dro,date_to

tapos ung custdetail nanjan po ung information about sa custromer then ang gusto ko po mangyari gusto ko kunin or palabasin ung data sa booking table


Eto po yung codes ko
View attachment 326583


yung customerdetail nagawa ko na po palabasin kaso ung ROOM NO , ARRIVAL , DEPATURE nd ko magawa palabasin ung data patulong nmn kung paano
View attachment 326588
View attachment 326585

View attachment 326584

View attachment 326586


View attachment 326583

 

[MrkMrk]

Use SQL JOIN, I'm not familiar sa java.
SQL JOIN query based on your database:
SELECT custdetail.*, bookingtable.date_fro, bookingtable.date_to FROM custdetail JOIN bookingtable ON custdetail.book_id = bookingtable.book_id WHERE cust_no = SomeNumber;
Let me try to explain it. Ginagamit ang SQL JOIN kung gusto mo magcombine ng rows from different tables based sa related columns nila (see: W3Schools explanation). The custdetail.* part, sineselect lahat ng columns ng custdetail table, bookingtable.column_name, sineselect lang yung specified na column which is date_from and date_to. ON ay yung related columns nila na dapat the same ang value. Yung SomeNumber, it should be the cust_no ng ivview mong customer. I'm not really familiar on java, but hope this helps. Goodluck!

 

[maskio]

may mali sa database structuring mo, pero para magka-idea ka kung paano sya e query, ganito yung sample:

SELECT a.cust_no, a.cust_name, b.book_id, b.room_no FROM custdetail a INNER JOIN bookingtable b ON a.bookId = b.bookId WHERE b.date_fro = 'your_date_here';

comments/suggestions:

add column "cust_no" sa booking details para ma 1 to many ang booking per customer.
if gagawin mo syang many customers to many booking, you need to add another table. table_customer_booking -> ang laman neto ang booking_id at cust_no, then e join mo ang tatlong table para ma many to many na sya.

 

[jhefreyzz]

So katulad ng sinabi ni @maskio mali yung pag structure mo sa database mo.

So suggestion lang:
Booking table:
book_id
cust_id
room_no
date_to
date_from
no_of_day

drop mo ung book_id mo sa customer table

then use INNER JOIN to fetch the customer data and the book data

like this: SELECT customer.*, booking.room_no, booking.date_to, booking.date_from FROM customer
INNER JOIN booking on customer.cust_id = booking.cust_id

Aside from the structure sa current database mo. Try mo i normalize ung tables mo.