SQL statement with multiple joins - unknown column error

[webdeveloper123]

Hi Guys,

I have a SQL statement that is trying to get details of Hotel rooms and it's amenities and create a result set from it. Also in the statement, I am returning rooms that are available from between the Dates which is coming from a basic HTML5 calendar that the user inputs from the search screen. I'm not showing the PHP because that bit is fine and also I'm focusing on the SQL and when I have a working statement I can build the PDO and swap out the hard coded dates for placeholders.  I have attached my ERD so it is easy for everyone to know what's going on. 

Here is the sql:

SELECT rs.description, rs.sleeps, rs.image, rs.price, f.description, roomtype.description
FROM roomsize AS rs
 JOIN room_facility AS rf ON rf.size_id = rs.size_id
 JOIN facility as f ON rf.facility_id = f.facility_id 
 JOIN room as r ON  room.roomtype_id = roomtype.roomtype_id 

WHERE room_booking.arrivalDate >= '2024-01-01'
 AND room_booking.departureDate < '2024-01-05';

And this is the error:

#1054 - Unknown column 'roomtype.description' in 'field list'

Now I know what the error means but I just can't fix it. As you can see from my ERD there is a table called roomtype and there is an attribute called description. Is it something to do with that the Joins are incorrect, or there is an error later in the statement which is causing the above error message? Or something to do with roomtype not being introduced earlier in the statement so It doesn't know what or where it is because I haven't told it something?

Test data:

roomsize (table name)

size_id   description            sleeps          image          price

1             Superior Queen        2               image.jpg    825.00

2             Superior Queen        2               image1.jpg   850.00

3            Superior Queen         4               image2.jpg   900.00

Note: It may seem odd that all 3 share the same description but the Amenities (called facilities here) are different and that will be prominent in the search result. So size_id 1 = Queen size bed, size_id 2 = Twin beds, etc etc. 

facility (table)

facility_id         description

1                       WI-FI

2                       55 Inch Smart TV

3                       Bottle of champagne on arrival

 

room_facility (table)

rf_id             size_id           facility_id

1                    1                       1

2                   2                       7

3                   3                      10

(Above is just a snapshot of data)

 

room (table)

room_id          floor       room_number         roomtype_id         size_id

1                       1             100                         1                              1

23                    7              700                        2                             7

31                     10            1000                     3                              10

 

Thanks for your help

 

 

[Barand]

You have four tables in your FROM clause (highlighted)

"roomtype" is not one of them, yet you are referencing its columns.

[webdeveloper123]

Ahh ok. I didn't know that. I thought when you were doing JOINS, just putting tablename.attribute  was sufficient, I didn't know you had to reference every single table in the JOINS for the attributes you are selecting.

So I did that, then I had a few more of the same errors. I was getting the same error for room_booking.arrivalDate so I did a join for room_booking. Then when I added that I got the same error again for booking.booking_id so I made a JOIN with booking table. Now I have referenced everything but I am still getting errors. I didn't even know what to JOIN booking too, so I joined it with room_booking, even though I have already made that JOIN with room_booking on the line before. It did seem that was obviously wrong but I just tried it to see if it would get rid of the error. Bit lost here and wondering how many JOINS do I need. Here is my code, could you help please?

 

SELECT rs.description, rs.sleeps, rs.image, rs.price, f.description, rt.description
FROM roomsize AS rs
 JOIN room_facility AS rf ON rf.size_id = rs.size_id
  JOIN room as r on r.size_id = rs.size_id
 JOIN facility as f ON rf.facility_id = f.facility_id 
 JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id 
 JOIN room_booking as rb on rb.booking_id = b.booking_id
 JOIN booking as b on rb.booking_id = b.booking_id


WHERE rb.arrivalDate >= '2024-01-01'
 AND rb.departureDate < '2024-01-05';

Error:

#1054 - Unknown column 'b.booking_id' in 'on clause'

Thanks

[Barand]

You don't need any data from the booking table so the last join can go.

At present your query does not know which rooms are booked, and when, as there is no join between room and room_booking tables. Your join to the room_booking table should therefore be ON r.room_id = rb.room_id

How do you know what facilities a room has? There is no room_id in the room_facility table. Instead you have a size id???

Your SELECT has no room_id or room_number so how will you identify the rooms in the results?

[webdeveloper123]

Hey Barand, thanks for the advice.

17 minutes ago, Barand said:

How do you know what facilities a room has? There is no room_id in the room_facility table. Instead you have a size id???

 

Yes but I decided to do the facilities based on size_id because each size_id have the same facilities. So it's not done on a per room basis, all size_id = 1 have all the same facilities. As with size_id 2 & 3.

22 minutes ago, Barand said:

Your SELECT has no room_id or room_number so how will you identify the rooms in the results?

I initially thought that I would not include room_number in the search results, but now I think it might be a good idea

I have tweaked the query and it is now running. Getting empty resultset but I'll investigate further. Post back in a bit.

Thanks

[Barand]

3 minutes ago, webdeveloper123 said:

Yes but I decided to do the facilities based on size_id because each size_id have the same facilities

Your model should then show room_facility joined to roomsize on size_id and not to room.

[webdeveloper123]

8 minutes ago, Barand said:

Your model should then show room_facility joined to roomsize on size_id and not to room.

Sorry I didn't understand. Are you referring to this line of code?

 JOIN room_facility AS rf ON rf.size_id = rs.size_id

 

[Barand]

5 minutes ago, webdeveloper123 said:

Are you referring to this line of code?

No. This...

[webdeveloper123]

ahh you meant my ERD. Thanks, I'll update it

[webdeveloper123]

Hey Barand,

Still can't return any search results. Been at it for hours now. I tweaked the SQL and added some more aliases as there were 3 x description attributes in the select. Here is my code:

 SELECT rs.description, rs.sleeps, rs.image, rs.price, f.description as rmfac, rt.description as rmtype, r.room_id, r.room_number
FROM roomsize AS rs
 JOIN room_facility AS rf ON rf.size_id = rs.size_id
  JOIN room as r on r.size_id = rs.size_id
 JOIN facility as f ON rf.facility_id = f.facility_id 
 JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id 
 JOIN room_booking as rb on  r.room_id = rb.room_id




WHERE rb.arrivalDate >= '2024-01-01'
 AND rb.departureDate < '2024-01-05';

Tried loads of different things like changing the dates, using different operators etc. It runs if I swap AND for OR but that's not right as the room has to be available between both dates, not just one. And it runs if I take out the WHERE clause.

 

Test data:

room_booking

room_booking_id             room_id            booking_id        arrivalDate                 departureDate

33                                      2                            1                  2023-08-10                  2023-12-31

34                                       4                           10                 2023-08-10                   2023-12-31

35                                      7                             5                   2023-08-10                   2023-12-31

This continues as 16/32 rooms are booked. All have the same arrival and departure Date. I did this because I knew I wouldn't finish the project within a week or so and I would have to keep inserting new data.

Thanks 

[Barand]

What is it that you want the results to show?

• rooms available between 2024-01-01 and 2024-01-05, or
• rooms not available between 2024-01-01 and 2024-01-05

I find a diagram helps. In the picture below, all 4 rooms are not available for the period 2024-01-01 to 2024-01-05 but your query wil only find room1 as that is the one that matches your WHERE conditions.

             12-30     12-31     01-01     01-02     01-03     01-04     01-05     01-06     01-07     01-08
    ......|.........|.........|.........|.........|.........|.........|.........|.........|.........|.........|
                              |                                                 |
                              |     +----------------------------+              |                                Room 1
                              |                                                 |
                +---------------------------------------------------------------------+                          Room 2
                              |                                                 |
                         +-------------------+                                  |                                Room 3
                              |                                                 |
                              |                        +---------------------------------------+                 Room 4
                              |                                                 |
                              |                                                 |

 

[webdeveloper123]

hmm I sort of get the diagram. It shows which room spans which dates they are occupied. 

I want to show rooms available between those dates. 

[Barand]

Then I think you want this method - using a LEFT JOIN to those bookings already made in the period and then the WHERE clause selects those that aren't matched

SELECT r.room_id 
     , r.number 
FROM room r
LEFT JOIN room_booking_2 rb
            ON r.room_id = rb.room_id
            AND rb.departureDate > '2024-01-01'
            AND rb.arrivalDate <= '2024-01-05' 
WHERE rb.room_id IS NULL
ORDER BY room_id;

or an alternative method  - using a subquery to find the rooms not available

SELECT r.room_id
FROM room r
WHERE room_id NOT IN   
    (
    SELECT room_id
    FROM room_booking_2 rb 
    WHERE departureDate > '2024-01-01'
          AND rb.arrivalDate <= '2024-01-05'
    );

 

[webdeveloper123]

Yes! it runs and I get search results! Thanks Barand! 😁

I took your logic from the code above and put it in the earlier sql statement further up the thread!