how to get id from joined table sql

Question

I have a joined table from siswa and kelas. in kelas table there is a column idSiswa, it comes from id from siswa table. the question is how I can get the id from kelas when its joined. when I try to fetch id it shows the id from siswa table, not from kelas table, I also already used right join and left join and still not get the answer

this is my kelas table

this is my siswa table

I using a query builder from laravel to run the query, and this is my query

$siswa = DB::table('siswas')
            ->join('kelas', 'kelas.idSiswa', '=', 'siswas.id')
            ->where([
                ['kelas.kelas', '2'],
                ['kelas.semester', 'ganjil'],

            ])
            ->select('kelas.*', 'siswas.*')
            ->get();

Answer 1

Your issue comes from a name conflict. When you join your two tables, there are 2 fields. To solve it, you should use SQL alias. You can see an example on this topic

You could also consider using Eloquent which offers OOP advantages and automatically avoids this kind of issues.

namespace App;

use Illuminate\Database\Eloquent\Model;

class Kelas extends Model
{
    public function siswa()
    {
        return $this->belongsTo('App\Siswa', 'idSiswa', 'kelas');
    }
}

namespace App;

use Illuminate\Database\Eloquent\Model;

class Siswa extends Model
{
    public function kelas()
    {
        return $this->hasMany('App\Kelas', 'idSiswa', 'kelas');
    }
}

$siswa = App\Siswa::with('kelas')
    ->where([
        ['kelas', '2'],
        ['semester', 'ganjil'],
    ])
    ->get();

$firstSiswaKelasIds = $siswa->first()->kelas->map->id;