当值不为null时,从另一个表调用值

I want to get the value from table stage_cdl if it has value and from tale stage_vdl if it has value.

到目前为止,我在这里所做的工作没有错误,但是没有值输出。我对sql很新。如您所见,我正在尝试从lcm和lvm中获取值。

$data = DB::table('invoice_stud as is')
            ->join('stud_stage_vdl as ssv', 'is.stud_id', '=', 'ssv.stud_id')
            ->join('stud_stage_cdl as ssc', 'is.stud_id', '=', 'ssc.stud_id')
            ->join('license_cdl_module as lcm', 'ssc.l_cdl_m_id', '=', 'lcm.id')
            ->join('license_vdl_module as lvm', 'ssv.l_vdl_m_id', '=', 'lvm.id')
            ->where('is.stud_id', $postData['stud_id'])
            ->select(
                'lcm.class_code',
                'lvm.class_code',
                'is.invoice_num',
                'is.stage_cdl_id',
                'is.stage_vdl_id',
                'is.total',
                'is.created_at'
            )
            ->get();
评论
心痛算什么
心痛算什么

Use COALESCE, and choose the first non NULL value from stage_cdl and stage_vdl (in that order):

$data = DB::table('invoice_stud as is')
        ->join('stud_stage_vdl as ssv', 'is.stud_id', '=', 'ssv.stud_id')
        ->join('stud_stage_cdl as ssc', 'is.stud_id', '=', 'ssc.stud_id')
        ->join('license_cdl_module as lcm', 'ssc.l_cdl_m_id', '=', 'lcm.id')
        ->join('license_vdl_module as lvm', 'ssv.l_vdl_m_id', '=', 'lvm.id')
        ->where('is.stud_id', $postData['stud_id'])
        ->select(
            'lcm.class_code',
            'lvm.class_code',
            'is.invoice_num',
            DB::raw('COALESCE(is.stage_cdl_id, is.stage_vdl_id) AS stage_id'),
            'is.total',
            'is.created_at'
        )
        ->get();
点赞
评论