wispas Posted December 30, 2009 Share Posted December 30, 2009 Hi, I currently have two tables in my MySQL database. City and Hotel are the tables. I have a bunch of hotels in the hotel table(some are in the same city locations... eg. New York) - they all have unique id's. In City table i have fields set up to place the hotel ids that that i want to display for that city: hotel_1 hotel_2 hotel_3 i set it this way so i can choose which hotels i want to display. I am trying to display this using the JOIN method... $result = mysql_query("SELECT city.city_id, city.city_name, hotel.hotel_name, hotel.hotel_address FROM city INNER JOIN hotel ON hotel.hotel_id = city.city_hotel_1"); This works but only displays one hotel. I want set up a query that will display all 3... PLEASE HELP ME! is there another method i can achieve. Quote Link to comment Share on other sites More sharing options...
JustLikeIcarus Posted December 31, 2009 Share Posted December 31, 2009 This is a common issue when laying out tables this way. Since your joining a table with a result that only has one record per city you are only going to get back one record per city. The fix would be one of a few options. One soltion would return all three hotels in each row returned. SELECT city.city_id, city.city_name, hotel1.hotel_name, hotel1.hotel_address, hotel2.hotel_name, hotel2.hotel_address, hotel3.hotel_name, hotel3.hotel_address FROM city, hotel hotel1, hotel hotel2, hotel hotel3 where hotel1.hotel_id = city.city_hotel_1 and hotel2.hotel_id = city.city_hotel_2 and hotel3.hotel_id = city.city_hotel_3 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.