How to optimize php + mysql code?

Question

PHP

Hello,
I have 2 tables,
The first lpu_nsi table stores organizations and their divisions, they all have the same mo_code
code number. The second table stores people, the mo_code code numbers of the organizations where they applied and the number of the organization's division in which they specifically applied.
I need to somehow find out the list where people applied and where they didn’t, the $param
variable is responsible for this, I wrote this code:

include ("connect.php");
$rez = mysqli_query($db,"SELECT mo_code, nompod, name_short FROM lpu_nsi");  //получаем список всех организаций и их подразделений
while ($nsi = mysqli_fetch_array($rez)){ //начинаем обход каждой записи
    $rz = mysqli_query($db, "SELECT COUNT(pa.id) AS nummo FROM patints_attachment pa WHERE pa.mo_code='$nsi[mo_code]' AND pa.nompod='$nsi[nompod]'"); // получаю число людей которые обратились в конкретное подразделение
    $count = mysqli_fetch_array($rz);
    if($param==1 and $count['nummo'] == 0){
        echo "<li role=\"presentation\">$nsi[name_short]</li>"; // вывожу если мне необходимо получить только те организации, куда никто не обращался
    }
    if($param==2 and $count['nummo'] != 0){
        echo "<li>$nsi[name_short]</li>"; // Вывожу если необходимо получить только те организации, куда обращались люди
    }
}

It turns out that I make a separate query for each record in the first table, I don’t like this solution and it is very slow, since there are 650 records in the first table, and 800,000 records in the second table, and there can be even more records in the second table, 2 times.
How can my code to optimize? I would like to do all this with 1 query to the database, and in the loop just display a list of those that MySQL returned to me, but how can I do this?

Answer 1

something tells me that you should be satisfied with Select with Join. Something like:

SELECT COUNT(pa.id) as nummo, nsi.name_short FROM lpu_nsi nsi,  patints_attachment pa  WHERE pa.mo_code=nsi.mo_code AND pa.nompod=nsi.nompod GROUP BY nsi.name_short