SQLSTATE[42000]: Syntax error or access violation: 1064- joins whit different tables

Question

I´m trying to code this with laravel:

$software = $row['DisplayName0'];
$equipo = $row['Netbios_Name0'];
//echo $software ."</br>";
$sqlconsulta2= "select DISTINCT NOMSOF, TIPLICSOF, TIPIMASOF, IDEMP, NOMEMP, APPEMP, APMEMP from equ,emp 
LEFT JOIN sof
ON sof.NOMSOF = '$software'
where IDEEQU = '$equipo'  and IDEEMP = IDEMP and pueemp = 'Empleado'";

I try a lot of forms to do that but I don´t know how to do it. I have this at the moment but it appears an error of syntax

 $lichome = DB::connection('mysql')->table('emp')->select('SOF.NOMSOF', 'SOF.TIPLICSOF', 'SOF.TIPIMASOF', 'EMP.IDEMP',
        'EMP.NOMEMP','EMP.APPEMP', 'EMP.APMEMP')
        ->where('pueemp', '=', 'Empleado')
        ->join('equ','EQU.IDEMP', '=', 'EMP.IDEEMP')
        ->whereIn('EQU.IDEEQU', $home->pluck('Netbios_Name0'))
        ->join('sof','NOMSOF', '=', $home->pluck('DisplayName0'))
        ->where('pueemp', '=', 'Empleado')->get();

Can anybody help me? Thank you.

what $home variable contains? $home->pluck will return array so the problem is that. — Mohamad Fazel Hesari, Mar 23 '20 at 20:31
$home = DB::connection('sqlsrv')->table('v_R_System_valid')->select ('v_R_System_valid.Netbios_Name0', 'v_ADD_REMOVE_PROGRAMS.DisplayName0', 'v_ADD_REMOVE_PROGRAMS.Version0', 'v_ADD_REMOVE_PROGRAMS.InstallDate0') ->join('v_ADD_REMOVE_PROGRAMS', 'v_R_System_valid.ResourceID', '=','v_ADD_REMOVE_PROGRAMS.ResourceID') ->where('v_R_System_valid.Netbios_Name0', '=', 'DMXL3211')->where('v_ADD_REMOVE_PROGRAMS.DisplayName0', '<>', '') ->orderBy('v_R_System_valid.Netbios_Name0', 'ASC')->distinct(); — HAMA, Mar 23 '20 at 22:11

score 0 · Answer 1 · answered Mar 23 '20 at 20:35

0

$home->pluck() method will return Illuminate\Support\Collection

Instead you must use $home->get($key)

answered Mar 23 '20 at 20:35

Mohamad Fazel Hesari

116
7

score 0 · Answer 2 · answered Mar 23 '20 at 21:15

You should use joins first, and then use the where clauses and also, using get().

 $lichome = DB::connection('mysql')
        ->table('emp')->select('SOF.NOMSOF',
                               'SOF.TIPLICSOF',
                               'SOF.TIPIMASOF',
                               'EMP.IDEMP',
                               'EMP.NOMEMP',
                               'EMP.APPEMP',
                               'EMP.APMEMP')
        ->join('equ','EQU.IDEMP', '=', 'EMP.IDEEMP')
        ->join('sof','NOMSOF', '=', $home->get('DisplayName0'))
        ->whereIn('EQU.IDEEQU', $home->pluck('Netbios_Name0'))
        ->where('pueemp', '=', 'Empleado')
        ->where('pueemp', '=', 'Empleado')->get();

You should know:

pluck() Retrieves all of the values for a given key

get() Returns the item at a given key. If the key does not exist, null is returned.

So, you can use pluck within whereIn() but you can not use it on a on clause within join, it's better to use get().

So, here:

->join('sof','NOMSOF', '=', $home->pluck('DisplayName0'))

You are doing something like:

...
INNER JOIN sof ON nomsof = ['item1', 'item2']

Also, I think you should do it like this:

->join('sof','sof.NOMSOF', '=', 'equ.SomeID')

SQLSTATE[42000]: Syntax error or access violation: 1064- joins whit different tables

2 Answers2