How to calculate number of foreign keys in second table with a condition and display it with rows from first table - PHP - MySQL? -

i have 2 tables tablea , tableb below;

tablea

+--------+-------+-------+-------+------+ |   fa   |   fb  |  fc   |  fd   |  fe  | +--------+-------+-------+-------+------+ |  col1  |  f11  |  f12  |  f13  |  x1  | +--------+-------+-------+-------+------+ |  col2  |  f21  |  f22  |  f23  |  x2  | +--------+-------+-------+-------+------+ |  col3  |  f31  |  f32  |  f33  |  x3  | +--------+-------+-------+-------+------+ |  col4  |  f41  |  f42  |  f43  |  x4  | +--------+-------+-------+-------+------+ 

tableb

+--------+-------+-------+------+------+ |  tbba  |  tbbb |  tbbc | tbbd | tbbe | +--------+-------+-------+------+------+ |  cola  |  fa1  |  fa2  |  0   |  x1  | +--------+-------+-------+------+------+ |  colb  |  fb1  |  fb2  |  0   |  x1  | +--------+-------+-------+------+------+ |  colc  |  fc1  |  fc2  |  1   |  x1  | +--------+-------+-------+------+------+ |  cold  |  fd1  |  fd2  |  1   |  x2  | +--------+-------+-------+------+------+ |  cole  |  fe1  |  fe2  |  1   |  x2  | +--------+-------+-------+------+------+ |  colf  |  ff1  |  ff2  |  0   |  x3  | +--------+-------+-------+------+------+ |  colg  |  fg1  |  fg2  |  1   |  x3  | +--------+-------+-------+------+------+ |  colh  |  fh1  |  fh2  |  1   |  x3  | +--------+-------+-------+------+------+ |  coli  |  fi1  |  fi2  |  0   |  x3  | +--------+-------+-------+------+------+ |  colj  |  fj1  |  fj2  |  0   |  x4  | +--------+-------+-------+------+------+ 

i want generate table like;

+--------+-------+-----+ |  col1  |  f11  |  1  | +--------+-------+-----+ |  col2  |  f21  |  2  | +--------+-------+-----+ |  col3  |  f31  |  2  | +--------+-------+-----+ |  col4  |  f41  |  0  | +--------+-------+-----+ 

this number of foreign keys in tablea tablea.fe in tableb tableb.tbbe, , tableb.tbbd field having value 1. had query like;

select a.fa , a.fb , count( b.tbbe)   tablea left join tableb b on a.fe = b.tbbe group a.fa 

but counts foreign keys without checking status of tableb.tbbd field. how can create table?

you may refer question here efficient way calculate number of foreign keys in second table , display rows first table - php - mysql

thank you.

the mistake in query never add condition join b.tbbd = 1. can add this:

select a.fa, a.fb, count(b.tbbe) tablea left join tableb b on a.fe = b.tbbe , b.tbbd = 1 group a.fa, a.fb; 

here sql fiddle example.