Slappy Slappy - 1 year ago 46
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 Source

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.