SQL Query Spanning Three Tables

[methuselah90]

Bear with me please because my SQL is not that good.

I want to write a query that takes into consideration information from 3 different tables.

Let me explain the situation: When a customer books a room, they have the preference of several rooms and the rooms can be booked on individual days (one unique booking has to be made per day) at different times.

I now want to write a query that will check if a room is available on a certain time of the day. These are the tables I have on my database:

room

This table stores information about each room

Fields:

> id (primary key)
> capacity    
> img 
> notes   
> building_id 
> roomstructure_id    
> lecturestyle_id

request

This table stores message about each request made

Fields

> id (primary key)
> day_id
> period_id
> preference_id (foreign key from preference.id)

preference

This table stores information about all the room preferences.

> id (PK)      
> request_id (foreign key from request.id)      
> room_id (foreign key from room.id)

I hope what I am trying to do has become apparent.

If for example I am checking whether room C233 is booked on Monday, 09:00 - I would need to look up the room information in the room table and then use the room_id in the preference table to look up the request_id.

Once I have the request_id which has been linked to a specific room (in the room preference table) I can see whether day_id=1 and period_id=1 in the request table. If day_id=1 and period_id=1 then it means that the room is booked at Monday, 09:00.

This means that room is not available so it isn't counted but if this row could not be found then it would be counted.

This is the SQL I've written so far - its very simple but it doesn't do everything I want it to do:

    SELECT COUNT(*) totalCount FROM ts_room

In addition to this, I have been offered advice on another forum - this code here. But it doesn't work at all. I don't think it meets my criteria:

SELECT  COUNT(*) totalCount
    FROM    room a
            INNER JOIN preference b
                ON a.id = b.room_id
            INNER JOIN ts_request c
                ON b.request_id = c.preference_id
    WHERE   c.day_id = 1 AND c.period_id = 1

Nothing returns when I run this query :-(

[LastMitch]

What is room a? I thought you table is just room?

I assume ts_room is your database

SELECT  COUNT(*) totalCount FROM ts_room 
JOIN preference 
ON room.id = preference.room_id 
JOIN ts_request 
ON preference.request_id = request.preference_id
WHERE request.day_id = 1 
AND request.period_id = 1

It's not tested, you can run the code there should be an error that tells you what is wrong and by that you can figure out what you need.

What are you trying to JOIN?

These are JOIN:

room.id = preference.room_id
preference.request_id = request.preference_id

[methuselah90]

Thanks for your response. I'm trying to find rows in which request.day_id and request_period_id do not equal 1 and do not exist. How can I go about doing this. Your SQL query looks like it is searching for rows where a match is found - I'm looking for rows where a match =1 isn't round.

[LastMitch]

Your SQL query looks like it is searching for rows where a match is found - I'm looking for rows where a match =1 isn't round.

Not found you mean?

Try this:

SELECT COUNT(*) totalCount FROM ts_room
JOIN preference
ON room.id = preference.room_id
JOIN request
ON preference.request_id = request.preference_id
WHERE request.day_id 
NOT IN (SELECT request.day_id FROM request)
AND request.period_id
NOT IN (SELECT request.period_id FROM request)

[adam_k]

The problem with the above query is that it needs records in both request and preference to count the rooms (inner join) and that it will probably create a cartesian product when it finds the same room in multiple requests. Additionally the where part will remove even those records, as it is eliminating all request.day_id (the day_id **stored **in request) from all day_ids stored in request. So basically the where is where 1 <> 1
which will never return true.

select count(*) as 'totalcount' from ts_room 
left join preference on ts_room.id = preference.room_id 
left join request on preference.request_id = request.id 
and request.day_id = @day and request.period_id = @period 
where request.id is null 

Please note:
1) I'm left joining to make sure that a ts_room will be returned if no preference or request record is found.
2) I'm looking for records that match the day and period, but eliminate them in the where. Since I'm left joining all ts_room records will be returned, the ones with request.id null (that didn't have a record with that room, day & period) will be kept.
3) I'm pretty sure that without the where clause I'm creating a cartesian product, so if too many rooms/preferences/requests exist it can be a little demanding on resources.

Also note that I haven't tested the above query and it might contain typos or other errors.