Home>

Table_A

code_A value_A
11 va1
12 va2
13 va3
14 va4
15 va5

Table_B

code_B value_B
1111 vb1
1113 vb2
1342 vb3
1355 vb4
1553 vb5

There is a table like the one above.
The first two digits of Table_B.code_B correspond to Table_A.code_A,

SELECT *
FROM Table_B AS B
LEFT JOIN Table_A AS A
ON B.code_B = TRUNCATE (A.code_A/100,0)


I would like to join the two under the conditions like
Is it possible to express this with leftJoin () of laravel Eloquant?

  • Answer # 1

    You can do this by passing a closure to the second argument of the join method, as described in "Advanced JOIN Statement" in the Query Builder of the document.

    $result = DB :: table ('Table_B as B')
                ->leftJoin ('Table_A as A', function ($join) {
                    $join->whereRaw ('B.code_B = TRUNCATE (A.code_A/100, 0)');
                })
                ->get ();

  • Answer # 2

      

    The first two digits of Table_B.code_B correspond to Table_A.code_A,

    This is tough.
    Is it better to add more columns to store the first two digits in the B table?

Related articles