I want to count the zero value too, on three table with foreign key

[enarco]

I using three table with foreign key, the in first table I add the fullname, and in second table I add product, and in three table I add the id number for both tables (first and second).

I want to count the how many product called for fullname table.
the my code not count the zero value, I ask how can I the count the zero value too

my code

<tbody>
<?php
$i = 1;
$qry = $conn->query("SELECT first.id, first.name, first.surname, second.id,  COUNT(*) FROM first JOIN three  ON first.id = three.fullnameid JOIN second  ON second.id = three.carerid GROUP BY name order by COUNT(*) desc ");
while($row= $qry->fetch_assoc()):
?>
<tr>
<th class="text-center"><?php echo $i++ ?></th>
<td><b><?php echo htmlentities($row['name']); ?></b></td>
<td><b><?php echo htmlentities($row['surname']); ?></b></td>	
<td><b><?php echo htmlentities($row['COUNT(*)']); ?></b></td>					
</tr>	
<?php endwhile; ?>
</tbody>

[ErnieAlex]

Don’t count using your version, instead do something like:

IFNULL(COUNT(*), 0) AS total

then display $row[‘total’] instead. something like that should work…

[enarco]

Thank you for your response, this code work like my code , but not count the zero value,

[ErnieAlex]

Show the new query you are testing. Did you change it in both sections? Meanin ORDER BY total ???

[enarco]

of course, I change it in both sections, but not work

[ErnieAlex]

Well, your logic is bad. You can not JOIN the first table with the second one ON a value not listed.
You check if an id in the first table exists in the second table. Or you can check if an id from the first table is in table three. But, you can not COUNT the data that does not exist.

The id in the first table is attached to a name.
The id in the second table is attached to a product/career.
The third table contains a mix of those. Where are you getting the zero’s in your sample output you want? I think you are missing something.

Perhaps you should tell us what you want.

[enarco]

Hello,
Thank you for your response,
yes,I am using 3 tables. first table for students’ information. The second table is for the courses students take. The third table to keep the ID of the first and second tables.

I want to know how many lessons each student took. With my codes, only the number of courses that students have taken, not the number of students who do not take courses. I want to see it too. So if the students have not taken any lessons, I want to write zero to it. I hope to explain clearly.
Thank you

[ErnieAlex]

Is this for a classroom project? Sounds like it. So, again the logic of your processes do not make a lot of sense. I will try to explain.

There are hundreds of ways to match tables against each other. JOIN’s are one. JOIN’s are actually three or four different ones depending on how you need the tables attached to each other. Also, quite often in SQL/MySQL/PDO, you have to select the results of a previous select. Sometimes you need to select a group of data and then join that results. The logic of the process makes the decision how to do that. Therefore, you have to think the logic out for what you really need. This is how you solve any programming task. If you get the logical process figured out, the coding of it is very simple.

In your case the most important thing you need is the full count of how many career classes each person used. That would be one select. So, the logic says, get that number first. Then, join the names of the class-career with that list and the total count will be zero for the ones not in the list. Not the other way around. In your final output, you do not even want the career listed, I mean the name, so it does not even need to be in the process at all. Does that make sense?

Now, the final process needs some new items added in. They are called “aliases”. You basically assign names to tables or results of selects. This is common in queries and lets you create a select or join and assign a temporary name to it. I used T1 and T3 for the new table names. These are temporary and have nothing to do with the actual tables or data, just a way to name groups of data in a query.

So, this solution should work for you:

SELECT T1.name, T1.surname, IFNULL(COUNT(T3.carerid), 0) AS total

FROM first AS T1

LEFT JOIN three AS T3
ON T1.id = T3.fullnameid

GROUP BY T1.id

I think this should work for you.

[enarco]

Thank you very much for your nice explanation, yes it makes sense.
Thank you again. The code works well, thanks

[ErnieAlex]

Glad I could help. And, glad it works!

It is always nice to solve a programming puzzle! See you at your next one!