How to make sql join query in laravel 5?

1
votes

Hi I want to get data from database, I am using join query but I have got this error:

QueryException in Connection.php line 673: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'surat_masuk' (SQL: select jenis_surat.jenis_surat, surat_masuk.nomor_surat, surat_masuk.perihal, surat_masuk.tanggal_publish from surat_masuk inner join jenis_surat on id_jenis_surat = jenis_surat.id_jenis_surat inner join surat_masuk on id_jenis_surat = surat_masuk.id_jenis_surat)

I have 2 tables, surat_masuk and jenis_surat, I want to get jenis_surat field from jenis_surat table and nomor_surat, perihal, tanggal_publish from surat_masuk table. and there is column id_jenis_surat in both table. This is my query:

$surat = new Surat();
    $surats = $surat->join('jenis_surat', 'id_jenis_surat', '=', 'jenis_surat.id_jenis_surat')
                    ->join('surat_masuk', 'id_jenis_surat', '=', 'surat_masuk.id_jenis_surat')
                    ->select('jenis_surat.jenis_surat','surat_masuk.nomor_surat','surat_masuk.perihal','surat_masuk.tanggal_publish')
                    ->get();

Do you know how to fix it?

2
mysqllaraveljoinlaravel-5jointable
can you share the relationship beetwen your model jenis_surat and surat_masuk ? - masadi zainul
Which table contain id_jenis_surat field in 'id_jenis_surat', '=', 'jenis_surat.id_jenis_surat' - Uttam Kumar Roy
Can you add the pure sql statement that you hope to achieve as a result of the above code? - Jeemusu
Use this way $surats = $surat->join('jenis_surat', 'surat_masuk.id_jenis_surat', '=', 'jenis_surat.id_jenis_surat') ->select('jenis_surat.jenis_surat','surat_masuk.nomor_surat','surat_masuk.perihal','surat_masuk.tanggal_publish') ->get(); - Uttam Kumar Roy
Thanks Uttam Kumar Roy! it's works. would you mind to answer in the answer field? :) - hendraspt

2 Answers

0
votes
$surats = $surat->join('jenis_surat', 'surat_masuk.id_jenis_surat', '=', 'jenis_surat.id_jenis_surat')
->select('jenis_surat.jenis_surat','surat_masuk.nomor_surat','surat_masuk.periha‌​l','surat_masuk.tanggal_publish')->get();
0
votes
$surat = new Surat();
$surats = $surat->join('jenis_surat', 'surat_masuk.id_jenis_surat', '=', 'jenis_surat.id_jenis_surat')
->select('jenis_surat.jenis_surat','surat_masuk.nomor_surat','surat_masuk.perihal','surat_masuk.tanggal_publish')
->get();

OR

$surats = \DB::table('surat_masuk')
->join('jenis_surat', 'surat_masuk.id_jenis_surat', '=','jenis_surat.id_jenis_surat')
->select('jenis_surat.jenis_surat','surat_masuk.nomor_surat','surat_masuk.perihal','surat_masuk.tanggal_publish')
->get();