我想通过book_id加入5个表。我试过了:
SELECT booked_room_info . * , booking_info . * , hotel_info.name as
hotelname,hotel_info.address as hoteladdress,hotel_info.contact as
hotelcontact , personal_info . *,room_registration.room_name as
roomname,room_registration.price as roomprice,room_registration.price as
roomprice,room_registration.description as
roomdescription,room_registration.image as roomimage
FROM booked_room_info
JOIN booking_info ON booking_info.booking_id = booked_room_info.booking_id
JOIN hotel_info ON hotel_info.id = booking_info.hotel_id
JOIN personal_info ON personal_info.booking_id = booked_room_info.booking_id
JOIN room_registration ON room_registration.hotel_id = booking_info.hotel_id
WHERE booked_room_info.booking_id= 1
但是,如果我在booking_id = 1上有2个预订,但它获取了10个结果。我想我应该做一个if条件。如果(booked.room_type && book_info.hotel_id)
相同,则只能从room_registration中获取行,但是如何完成它。
booked_room_info
------
id | booking_id | room_type | check_in | check_out
1 | 1 | delux | 2015/1/2 | 2015/1/5
booking_info
---------
id | booking_id | hotel_id | user_id
1 | 1 | 2 | 1
hotel_info
----------
id | name | address | user_id
2 |palm hotel | newyork | 1
personal_info
-------------
id |full_name | address | nationality | booking_id
1 | sushil stha | new york | smth | 1
room_registration
-----------------
id | room_name | price | image | hotel_id | user_id
1 | delux | 1000 |room.jpg | 2 | 1
您上一次加入时发生错误:
JOIN room_registration ON room_registration.hotel_id = booking_info.hotel_id
您正在使用hotel_id
列联接这些表。room_registration
表格中可能有5行带有hotel_id=2
。因此,加入后您会收到2*5=10
行,而不是预期的2。
因此,您应该以不同的方式加入该表。
如果要用room_registration
表格中的每一行连接每一行,则必须指定更精确的连接条件。
为了room_registration
正确地连接表,您可以room_registration_id
在booking_info
表中添加列。然后,您可以使用以下方法加入:
JOIN room_registration ON room_registration.id = booking_info.room_registration_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句