CODEWITHSUNDEEP
mysqllaraveljoin
mohit
mohit

Reputation: 290

SQL join issue with Laravel

i have 2 tables where First table have all the values of category but i dont want to show all categories to every customer so there is a column is_hidden the structure is 

cat_id    cat_name    is_hidden
  1         cat         no
  2         cat1        no
  3         cat2        no
  4         cat3        no
  5         cat4        yes
  6         cat5        yes

Now in my SQL query i am getting all the values where is_hidden column is no In my other table i am approving some clients to show the category The table structure is

id   cat_id    client_id
1     5          1

Now i want to display all the campaign from table where is_hidden is no and now that i have approved the client in table 2 so it should also show the category id from 5

Now how can i join the SQL to get the desired result.

Here is my code what i have tried.

Approvals::where('client_id',$client_id)
            ->rightJoin('tbl_cat', function ($join) {
                $join->on('tbl_cat.cat_id', '=', 'tbl_approval.approval_id');
                $join->where('tbl_cat.is_hidden','=','no');                
            });

But i am not getting the category 5 in output.

Upvotes: 0

Views: 56

Answers (2)

Nikita
Nikita

Reputation: 437

Your join should be on cat_id so that you will get records of cat_id 5. Hope it will help you.

Approvals::where('client_id',$client_id)
            ->rightJoin('tbl_cat', function ($join) {
                $join->on('tbl_cat.cat_id', '=', 'tbl_approval.cat_id');
                $join->where('tbl_cat.is_hidden','=','no');                
            });

Upvotes: 0

Hardeep Singh
Hardeep Singh

Reputation: 780

this can't be achieve by join. you should use union all like following

SELECT
    *
FROM
    table1
WHERE 
    is_hidden = 'no'
UNION ALL
    SELECT
        t.*
    FROM
        table2 t2 INNER JOIN table1 t ON t.id = t2.cat_id
    WHERE
        t2.client_id = 1

Upvotes: 1

Related Questions