Slappy Slappy - 6 months ago 8
PHP Question

SQL Query returning null on a column with info

I've got a few different tables, one which is titled "hotel" and contains information in regards to a chain of hotels and another which is titled "room" which contains information about the rooms within the hotels.

I'm trying to query the database for a list of the hotel names along with the room numbers ordered in a certain way. Ordering them is easy, but I've had trouble trying to hack up some code to make this work.

What I've got is:

SELECT
hotelName,
roomNo

FROM
hotel

RIGHT JOIN
room

ON
hotel.hotelName=room.roomNo


This works, it joins the tables and presents all the room numbers but all the hotel names are returned as null. I've been working on this for a few days now, its part of my study, but I just can't work out how to get it to work. The tables aren't connected via and keys but as far as I can tell that shouldn't matter, and it didn't matter with the normal and left JOINs.

Cheers for any help on this one guys :)

This is the database presented in the designer on phpMyAdmin and the result I'm getting with the current code. The only difference I need is that the hotel name shows the actual hotel names stored in the database.

enter image description here

Answer

You are linking the hotel and the room via hotel-name and room-number, i think it should be:

SELECT
    hotelName,
    roomNo

FROM
    hotel

INNER JOIN
    room

ON
    hotel.HotelNo = room.HotelNo 

The right join makes only sense if you can have hotel-rooms which don't belong to hotels, so i've used an INNER JOIN.

Of yourse the name of a hotel is not a good primary key. You should define a HotelID instead. A hotel-name and a room-number seem to be two completely different things. Update The new image of your db shows that it's HotelNo

I guess you have forgotten to add a foreign-key-column to hotel in your room table. HotelName is a bad candidate because multiple hotels with the same name are possible. You need a unique identifier. Create a primary-key column in hotel(f.e. HotelID) and a FK column to hotel in room(hotel a room belongs to). You could name this column also HotelID.