Get only specific data based on query

I am new to Laravel.

I have a table 'schedules' with 4 data on it. You'll notice that 3 out 4 data on the table have same event_staff_id which is id 6 but they have different event dates.

Now what I want to query is like this, if the user inputted 2022-05-29 in the front-end then the return data should be only 1 which is the event_staff_id 7 since that's the only user without event date that matches to user's input. Meaning event_staff_id 7 is available to the user.

Hope you can help me on this. Note that I really don't have any idea on how to query this that's why I don't have code to show to you guys.

Thanks

CodePudding user response：

With standard mySQL i will try with this:

SELECT * FROM 'db_name'.'table_name' where event_staff_id not in (select event_staff_id from 'db_name'.'table_name where event_date= '2022-05-29') GROUP BY event_staff_id;

in laravel i think that can be this: DB::table(..)->select(..)->whereNotIn('event_date','2022-05-29')->get();

CodePudding user response：

With standard mySQL i will try with this:

SELECT * FROM 'db name'.'table name here' where event_staff_id not in (select event_staff_id from 'db_name'.'table_name' where event_date= '2022-05-29') GROUP BY event_staff_id;

in laravel i think that can be this:

DB::table(..)->select('event_staff_id')->whereNotIn('event_staff_id',function(){ $query->select('event_staff_id')->from(put table name)->where('event_date','2022-05-29');

})->get();