How to select two additional columns from another table based on values in the base table

Below is sample SQL showing how we can use additional columns from another table based on value of base table.

SELECT base_table.id, another_table.id AS some_other_name, another_table.name
FROM base_table AS base_table
  INNER JOIN (SELECT id, name FROM another_table) another_table
    ON base_table.another_table_id = another_table.id
WHERE another_table.some_reference = 15
ORDER BY base_table.id DESC
LIMIT 10

Laravel join or left join queries AS different name

There are many situations where you need to join same table, but must have different names (you can define this as alias). Below is a simple code snippet to show how you can join same table with different name.

$limit = 10;
$page = 1;
$groups = DB::table('person')
       ->leftJoin('users as for_user', function ($join) {
           $join->on('for_user.id', '=', 'person.for_id')->where("for_user.id", "=", 10);
       })
       ->join('users as by_user', function ($join) {
           $join->on('by_user.id', '=', 'person.by_id');
       })
       ->where('person.status', '=', 1)
       ->select('person.id', 'person.first_name', 'by_user.first_name')
       ->limit($limit)->offset($page * $limit)
       ->get();

Hibernate/grails left join or inner join when using create criteria

Hibernate/grails left join or inner join or join when using create criteria. If you do not use

'CriteriaSpecification.LEFT_JOIN', then it would be a inner join. 

Here is the example as suppose 'User' belongsTo 'Role' which is nullable.

Now you want to left join 'Role' when creating criteria on 'User', then do 

the following. It would take all 'User' row where 'Role' is null or not.

But if you do not use 'CriteriaSpecification.LEFT_JOIN', then it would take only

'User.role' is not null. 

import org.hibernate.criterion.CriteriaSpecification;

List userList = User.createCriteria().list {
    eq("isActive", true);
    createAlias("role", "__role", CriteriaSpecification.LEFT_JOIN)
    eq("__role.someProperty", "some value");
}