Search code examples
mysqlleft-joinright-join

mysql left join / right join 3 tables

I have 3 tables in relationship

1. table privilege (get user privilege from username)

select branch_code,username from privilege where username='joko';

Results:

| branch_code  | username |
|--------------|----------|
| 'IDJK003MPV' | 'joko'   |
| 'IDJK001MAG' | 'joko'   |
| 'IDJK002MCL' | 'joko'   |
| 'IDBTNCTR'   | 'joko'   |

2. table biodata_karyawan (this table about employees with branch_code)

select branch_code,status_karyawan from biodata_karyawan;

Results:

| branch_code  | status_karyawan |
|--------------|-----------------|
| 'IDJB001BEC' | 'Aktif'         |
| 'IDJB001BEC' | 'Aktif'         |
| 'IDPA001PPA' | 'Aktif'         |
| 'IDJK001MAG' | 'Aktif'         |
| 'IDJB001BEC' | 'Aktif'         |
| 'IDJB001BEC' | 'Tidak Aktif'   |
| 'IDBTNCTR'   | 'Tidak Aktif'   |

3. table branch

select branch_code,branch_name from branch;

Results :

| branch_code  | branch_name              |
|--------------|--------------------------|
| 'IDJB001BEC' | 'BEC BANDUNG'            |
| 'IDJK001MAG' | 'MALL ARTHA GADING'      |
| 'IDJK001HO'  | 'HEAD OFFICE'            |
| 'IDPA001PPA' | 'MALL MATAHARI JAYAPURA' |
| 'IDBE001BGK' | 'BENCOOLEN INDAH MALL'   |
| 'IDJK002MPB' | 'METRO PASAR BARU'       |

I Hope the results would be like this guys : (records just sample only)

| branch_code  | branch_name    | count |
|--------------|----------------|-------|
| 'IDJB001BEC' | 'MALL BANDUNG' | '5'   |
| 'IDJK001MLP' | 'MALL LIPPO'   | '2'   |
| 'IDJK002MPI' | 'MALL PURI'    | '0'   |
| 'IDJB002ZZZ' | 'MALL POSO'    | '0'   |

Can anyone can help me ?
Thanks for your time.

Solution

  • Use count on status_karyawan and then group by 

    select bk.branch_code,b.branch_name,count(bk.status_karyawan) from
  biodata_karyawan bk join
  branch b on bk.branch_code = b.branch_code
  group by bk.branch_code,b.branch_name

    Here is sql fiddle

    Updated Query as per your comment

    select p.branch_code,b.branch_name,count(bk.status_karyawan) from
 biodata_karyawan bk join
 branch b on bk.branch_code = b.branch_code
 join privilege p on b.branch_code = p.branch_code
 Where p.username = 'joko'
 group by p.branch_code,b.branch_name

    Update fiddle