I have a table of contracts and here is the example data
id spec1 spec2 spec3 1 2 4 3
And i got another table of spec and the values are
id text 1 hello 2 hi 3 hola 4 yow
how can i get the text from spec table? do i need to multiple join them? what I want is something like
{ contract.id: 1, spec1: hello, spec2: yow, spec3: hola, } { contract.id: 2, spec1: hello, spec2: yow, spec3: hola, }
im selecting from contracts table
I tried
DB::table('contracts as c') ->join('spec','spec.id','c.spec1') ->join('spec','spec.id','c.spec2') ->join('spec','spec.id','c.spec3') ->get();
but its wrong and got some errors. Thanks in advance for the help.
Advertisement
Answer
try below code:
DB::table('contracts')->select('contracts.id','a.text AS spec1','b.text AS spec2','c.text AS spec3') ->leftJoin('specs AS a', 'a.id', 'contracts.spec1') ->leftJoin('specs AS b', 'b.id', 'contracts.spec2') ->leftJoin('specs AS c', 'c.id', 'contracts.spec3') ->get();
MySQL documation
Read this Question