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.


Comments

Popular posts from this blog

javascript - gulp-nodemon - nodejs restart after file change - Error: listen EADDRINUSE events.js:85 -

Fatal Python error: Py_Initialize: unable to load the file system codec. ImportError: No module named 'encodings' -

javascript - oscilloscope of speaker input stops rendering after a few seconds -