How to write subqueries in laravel?

Question

I am very new to the laravel subqueries , i am trying to fetch another table data but it's showing an following error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT tl_titlename FROM bm_titlelist WHERE tl_titleid=1 from `bm_customercon...' at line 1 (SQL: select SELECT tl_titlename FROM bm_titlelist WHERE tl_titleid=1 from `bm_customercontactinfo`)

query

   DB::enableQueryLog();
        $data = DB::table('bm_customercontactinfo')
                    ->select(
                            DB::raw("SELECT tl_titlename FROM bm_titlelist WHERE tl_titleid=1")
                    )
                    ->get();
        dd(DB::getQueryLog());

can anyone help me where did i mistake ?

Your Laravel logic does not make much sense. Can you edit your question and include the raw MySQL query which you are trying to achieve in Laravel? — Tim Biegeleisen, Jan 10 '23 at 03:31
Check I hope this is helpfull for you. https://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in — Irshad Khan, Jan 10 '23 at 04:41
Please edit your question exactly what kind of result you need using subquery — Bhatt Akshay, Jan 10 '23 at 05:28

score 0 · Answer 1 · answered Jan 10 '23 at 05:41

I think the issue is how you call the select method

You can check the documentation https://laravel.com/docs/9.x/queries#select-statements

Though I'm not sure about your expected result, I assume that you are trying to get a specific title for each customer info result. You can use join for that case. Please follow the documentation here. https://laravel.com/docs/9.x/queries#joins

score 0 · Answer 2 · answered Jan 17 '23 at 12:48

You can use this

$data = DB::table('bm_customercontactinfo')
    ->join(DB::raw("(SELECT tl_titlename, tl_titleid FROM bm_titlelist) as sub"), function($join)
    {
        $join->on("sub.tl_titleid", "=", "bm_customercontactinfo.title_id");
    })
    ->select("bm_customercontactinfo.*", "sub.tl_titlename")
    ->get();

How to write subqueries in laravel?

2 Answers2