CODEWITHSUNDEEP
mysqlpdo
methuselah
methuselah

Reputation: 13206

SELECT value from table because two conditions are not met

I have three tables on my database:

ts_room

Fields:

id (PK)
capacity    
img 
notes   
building_id 
roomstructure_id    
lecturestyle_id

ts_request

Fields

id (PK)
day_id
period_id
roompref_id (FK > ts_roompref.id)

ts_roompref

id (PK)      
request_id (FK > ts_request.id)      
room_id (FK > ts_room.id)

I would like to write a MySQL PDO query that selects rows from ts_room provided that after running the rows on ts_roompref (checking ts_roompref.room_id against ts_room.id) and finding a match - we would then look up the value in ts_request and see whether day_id and period_id both match 1. It would return the total count.

I hope the above explanation makes sense.

Essentially - requests for rooms are made with this system and ts_roompref stores the room preferences made for each request. I am trying to find out whether a particular room is booked on a day and period (denoted by day_id and period_id in the ts_request table), in this case Monday (1) and period (1).

Upvotes: 0

Views: 97

Answers (1)

John Woo
John Woo

Reputation: 263733

how about this?

SELECT  COUNT(*) totalCOUnt
FROM    ts_room a
        INNER JOIN ts_roompref b
            ON a.id = b.room_ID
        INNER JOIN ts_request c
            ON b.request_ID = c.roompref_ID
WHERE   c.day_ID = 1 AND c.period_ID = 1

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions