MySQL Joining Tables - Display (PLEASE HELP)

[wispas]

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.

[JustLikeIcarus]

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