MySQL Table Join (Help with Query)

[wispas]

Database name:

Citybreak

 

CITY TABLE

city_id, city_name, city_desc, hotel_1, hotel_2, hotel_3)

 

HOTEL TABLE

hotel_id, hotel_name, hotel_desc

 

so i have inserted a hotel_id into hotel_1, hotel_2 and hotel_3 as this will allow me to choose which hotels i want to display for that city.

 

now i want to write a sql query to say:

 

if your displaying a city it will also display the 3 hotels we defined with the hotel_1, hotel_2 and hotel_3 fields in the city table.

 

Any Ideas?

 

I have tried this query so far:

SELECT city.city_id, city.city_name, hotel.hotel_name FROM city, hotel INNER JOIN hotel WHERE hotel.hotel_id = city.city_hotel_1

 

This gives me one hotel, but i want to show all 3.

[premiso]

You are better off making a third table - city_hotels and using that.

 

New table structure:

CITY -

    city_id, city_name, city_desc

HOTEL -

    hotel_id, hotel_name, hotel_desc

CITY_HOTEL -

    ch_id, city_id, hotel_id

 

Then the query would be something like:

 

SELECT c.city_id, c.city_name, h.hotel_name 
FROM city c JOIN city_hotel ch ON c.city_id = ch.city_id JOIN hotel h ON h.hotel_id = ch.hotel_id 
ORDER BY c.city_name, h.hotel_name;

 

The reason you were only getting one hotel from your original query was because you were only selecting one in the WHERE clause. A simple modification to that WHERE clause and it will pull all cities / hotels. You are also adding the hotel table twice. Here is a correct version of your code, incase you do not care about 3NF form: (the below query is untested)

 

SELECT c.city_id, c.city_name, h.hotel_name
FROM city c, hotel h 
WHERE h.hotel_id IN(c.hotel_1, c.hotel_2, c.hotel_3)
ORDER BY c.city_name, h.hotel_name;

 

Hope that helps you out.

 

 

[wispas]

THAT IS PERFECT!

 

thank you so much... i will discuss about adding a extra table with my manager. but for now the bottom method works well... thank you!