CODEWITHSUNDEEP
phpmysqllaravel
Rashed Hasan
Rashed Hasan

Reputation: 3751

select all columns which are not in another table laravel 5.5

I have two tables - the first one is called 'users' and the second one is called 'buy_courses'.

users table

enter image description here

I am trying to select all users those user_name is not in buy_courses. I tried something like -

$users = DB::table('users')
                ->rightjoin('buy_courses', 'users.user_name', '=', 'buy_courses.user_name')
                ->get();

It returns all users, whose user_name is in 'buy_courses', when I am using '<>', then I'm getting all users. What should be the right query?

Upvotes: 11

Views: 23179

Answers (4)

Teduh Afriyoko
Teduh Afriyoko

Reputation: 11

just replace = with != , use function join

$users = DB::table('users')
  ->join(
    'buy_courses', 
    function ($join)
    {$join->on('users.user_name', '!=', 'buy_courses.user_name');}
  )
  ->get();

Upvotes: 1

anayarojo
anayarojo

Reputation: 1205

Try it using Eloquent:

$courseUserNames = BuyCourses::pluck('user_name')->all();
$users = User::whereNotIn('user_name', $courseUserNames)->select(...)->get();

Or if you prefer using DB query:

$courseUserNames = DB::table('buy_courses')->pluck('user_name')->all();
$users = DB::table('users')->whereNotIn('user_name', $courseUserNames)->select(...)->get();

Upvotes: 7

azbatuk
azbatuk

Reputation: 293

You can use SQL's 'NOT IN'.

Example:

mysqli_query($con, "SELECT * FROM users WHERE user_name NOT IN (SELECT user_name FROM buy_courses)");

Upvotes: -2

Markownikow
Markownikow

Reputation: 457

DB::table("users")->select('*')->whereNotIn('user_name',function($query) {

   $query->select('user_name')->from('buy_courses');

})->get();

just join actually is inner join in Laravel so actually maybe also you can try:

DB::table('users')
            ->join('buy_courses', 'users.user_name', '=', 'buy_courses.user_name')
            ->get();

Upvotes: 18

Related Questions