Retrieve only the users I don't have in another table

Question

I want to retrieve the users (from users table) that aren't in any group yet (group_user table) and are etudiant type (users.type="etudiant")

I tried this in my function but it give me repeted users from both tables

        $Othersetudiants=DB::Table('users')
        ->join('group_user', 'users.id', '!=', 'group_user.user_id')
        ->select('users.*')
        ->where([['users.type','=','etudiant'],
        ['users.id', '!=', 'group_user.user_id']
        ])
        ->get();

Here is my data and tables all foreign keys are set in migrations..

Group:

group_user:

user:

score 0 · Answer 1 · answered Apr 16 '20 at 18:17

The query built is:

select users.*
from users
[inner] join group_user on 
   users.id != group_user.user_id
where
   users.type = 'etudiant'
   and users.id != group_user.user_id

The problem here is in the join clause, you are matching one row of the user table with all rows in the group_user table, except the row that satifies the clause users.id = group_user.user_id.

If you only want the users that are not in the group_user table, you might use LEFT JOIN and then "filter" the users that didn't match the left join:

select users.*
from users
left join group_user on 
   users.id = group_user.user_id
where
   users.type = 'etudiant'
   and group_user.user_id is null

For more detailed explanation, see What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Retrieve only the users I don't have in another table

1 Answers1