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 two tables tablea and tableb like 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 to generate a table like;

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

This is the number of foreign keys in tablea tablea.fe in tableb tableb.tbbe, and with tableb.tbbd field having value 1. I had a query like;

SELECT a.fa , a.fb , COUNT( b.tbbe) FROM tablea a LEFT JOIN tableb b ON a.fe = b.tbbe GROUP BY a.fa

But this counts all foreign keys without checking the status of tableb.tbbd field. How can I create this table?

You may refer my another question here Efficient way to calculate number of foreign keys in second table and display it with rows from first table - PHP - MySQL

Thank you.

--------------Solutions-------------

The mistake in your query is simply that you never add a condition to also join where b.tbbd = 1. You can add it like this:

SELECT a.fa, a.fb, COUNT(b.tbbe)
FROM tablea a
LEFT JOIN tableb b ON a.fe = b.tbbe AND b.tbbd = 1
GROUP BY a.fa, a.fb;

Here is an SQL Fiddle example.

If b.tbbd is a only ever 0 or 1 then you can use SUM() instead of COUNT() which will exclude the rows where tbbd = 0.

SELECT a.fa , a.fb , SUM( b.tbbe)
FROM tablea a
LEFT JOIN tableb b ON a.fe = b.tbbe
GROUP BY a.fa

If b.tbbd can be any number but you're just looking for the rows where it equals 1 then just add an AND to your ON clause

SELECT a.fa , a.fb , COUNT( b.tbbe)
FROM tablea a
LEFT JOIN tableb b ON a.fe = b.tbbe AND b.tbbd = 1
GROUP BY a.fa

Category:php Time:2018-11-07 Views:1
Tags: php mysql

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.252 (s). 13 q(s)