CODEWITHSUNDEEP
phpmysqlsqllaravel
Malouda
Malouda

Reputation: 155

Check if data exists on either table if not select from one table

I have two tables one for rooms and the other one for room status which contains the status of the room if busy or not,now i have this sql which checks all rooms in the rooms table but returns the results for only those rooms in the room_status table which are free

 $results=DB::select( DB::raw("SELECT r.room_id,r.room_no,r.description,r.path,r.room_photo,r.price,r.free_services,s.is_free FROM rooms r,roomstatus s
                                        WHERE r.room_id NOT IN ( SELECT b.room_id FROM roomstatus b
                                        WHERE NOT ( b.end_datetime <   :start_date OR b.start_datetime > :end_date ) ) OR is_free=1
                                        ORDER BY r.room_id"),array('start_date'=>$start_date,'end_date'=>$end_date));

The problem is the query does not return any room if its id is not in the room_status table,i want it to check all rooms in the rooms table if their correspondence id is not in room_status table or if their id is in the room_status table and is marked free

Upvotes: 0

Views: 97

Answers (2)

StephenMtl
StephenMtl

Reputation: 438

An easier way to accomplish what you are trying to do would be to preform a join on the tables.

Something along the lines of

$results = db :: SELECT
(
    db :: raw (
    "
        SELECT r.room_id, r.room_no, r.description, r.path, r.room_photo, r.price,
        r.free_services, s.is_free 
        from rooms r left join roomstatus s 
        on (s.room_id = r.room_id) 
        where s.room_id is null or s.is_free = 1
    "));

This query would return all rooms for which there is no status (no record exists in status table) or for rooms with the is_free flag set to true. The key here is using a left join, so that you include all the records from your rooms tables regardless of if there is a record in the status table. Using this a base query, you can expand the where clause to suit your needs, IE filtration by date.

Upvotes: 1

Bardh Lohaj
Bardh Lohaj

Reputation: 1410

You can use left join like this:

$results = db :: SELECT
    (
        db :: raw (
            "SELECT 
                r.room_id,r.room_no,r.description,r.path,r.room_photo,r.price,r.free_services,s.is_free 
            FROM rooms r
            LEFT JOIN roomstatus s
            WHERE s.is_free = 1 OR s.is_free is null                    
            ORDER BY r.room_id"
        )
    );

You can add the start and end date on top of this if thats necessary.

Upvotes: 2

Related Questions