Mysql: Select rows from a table that are not in another

12 Jul

To get All records that are exists in one table but not exits in Another table.
To achieve this we need to user left join

We have one users table
users

id
user_name
email
Full_name

And second table is roles

roles
—-
id
user_id
role

We can write the query like this.


SELECT users.id,users.user_name,users.email,users.full_name FROM users
LEFT JOIN roles ON roles.user_id = users.id AND role IS NULL

 

Same thing can be achieved in Laravel.


$user_row = DB::table('users')
->select("users.id" ,"users.user_name")
->leftJoin('roles','roles.user_id', '=', 'user.id')
->WhereNull("roles.role")
->get();

And if you want to count that How many rows are in one table that not in another tables then you can write this query like this.


SELECT users.id,count(*) FROM users
LEFT JOIN roles ON roles.user_id = users.id AND role IS NULL
group by roles.user_id

 

Mysql Update value with join clauses

UPDATE lead_fields
JOIN field_types
ON field_types.id = lead_fields.field_type_id
SET lead_fields.field_type = field_types.field_type

 

Hope This will help you.

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

4 × 1 =