I need to get all records from table IF these records not exists in another table, e.g.
Table 'Cars'
| ID | Name_of_car
+----+----------------
| 1 .| Toyota
+----+----------------
| 2 .| Ford
Table 'Crashed_cars'
| car_id | crash_date
+--------+----------------
| 1 .....| 22-02-2016
Now I want to get all not crashed cars - how can I do this?
At the moment I use a loop, but due to the number of entries (about 3000) I would have to use the direct collection. Here is my query:
Cars::select( 'id', 'firstname', 'lastname', 'car', 'color' )->get();
The best way is probably to get the ids of all crashed cars, and then searching for all other cars. In other words, something like this:
$crashedCarIds = CrashedCar::pluck('car_id')->all();
$cars = Car::whereNotIn('id', $crashedCarIds)->select(...)->get();
You could also get the same result with one single query, which will increase the speed of your action.
One could use a FULL OUTER JOIN to achieve this, like this example from the following article:
select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null
Article: http://www.datamartist.com/sql-inner-join-left-outer-join-full-outer-join-examples-with-syntax-for-sql-server no. 6.
Good luck!