mysql - SELECT from table returning one set of value but ignoring secondary condition -

mysql - SELECT from table returning one set of value but ignoring secondary condition -

i'm trying write sql statement checks availability of rooms on database. needs accomplish following:

1) check ts_roompref, ts_room , ts_request see if room booked

if room not booked @ day_id , period_id in ts_request not found when ts_room_pref.request_id checked in ts_roompref , compared ts_request.id. in case room entry cannot found (i.e. has not been requested) room free

2) if day_id , period_id value found after comparing ts_request , ts_roompref means need ts_allocation table check status of room

if request status "pending,failed,declined" means room has not been allocated yet , still available but if request status = else means room has been allocated.

i've been struggling few hours trying implement logic. i've done far works:

select 1- count(distinct rp.id) ts_roompref rp, ts_request rq rp.room_id= "d.0.02" , rq.id = rp.request_id , day_id "1" , period_id "1";

the above code not take consideration fact may pending, allocated or declined. assumes room booked if request_id can found. how can improve check request status (if necessary)?

here sql fiddle: http://sqlfiddle.com/#!2/e1021/48

there should percentage signs near "like" conditions in query

... day_id "%1%" , period_id "%1%"

mysql