CODEWITHSUNDEEP
mysqljsonleft-join
user2552863
user2552863

Reputation: 499

Mysql Join Two tables and get results with If condition

In my application user can select multiple banks. the user can select multiple banks that he has credit card. The bank list is in the bank table. the user selected banks are insert to mobile_user_bank table. In mobile_user_bank table columns are id, mobile_user_id, bank_id.

Two tables

  1. banks - Save a bank list

  2. mobile_user_bank - save user selected banks.

I want to get a json object that give a all the banks list with a variable is_selected.

  `{
        "id": 2,
        "bank_name": "HNB",
        "image": "bank_hnb.png",
        "is_selected": 1
    },
    {
        "id": 3,
        "bank_name": "BOC\n",
        "image": "bank_boc.png",
        "is_selected": 0
    },
    {
        "id": 4,
        "bank_name": "People's Bank",
        "image": "people.png",
        "is_selected": 1
    }`

I tried this query but result is wrong, not give all the banks but only user selected banks are repeating. 

SELECT ub.id, ub.name, ub.image FROM banks ub left join mobile_user_banks mub on ub.id = mub.bank_id where mub.mobile_user_id =25;

Upvotes: 0

Views: 50

Answers (1)

Rakesh Kumar
Rakesh Kumar

Reputation: 4420

Use if condition for it, your query is almost complete - 

SELECT ub.id, ub.name, ub.image, if (mub.bank_id is null, 0 , 1) as is_selected
FROM banks ub 
left join mobile_user_banks mub on ub.id = mub.bank_id 
and mub.mobile_user_id = 25 ;

https://paiza.io/projects/G7GOjsR03mqygV3fkeL6CA?language=mysql enter image description here

Upvotes: 1

Related Questions