Compare two fields between different tables

Question

I have a big problem. This is the following I have the Script below:

<?php
$contarperfilclientecontar = "0";
$sqlxml = "SELECT id
                  ,vvenda
             FROM imoveis
            WHERE cod = '1042'
              AND status = '2'
          ";
$rsqlxml = mysql_query($sqlxml) or die("Banco XML não abre!");

while ($rowxml = mysql_fetch_array($rsqlxml))
    {
    $vartestevalor = $rowxml['vvenda'];
    $sqlxmlcliente = "SELECT id
                        FROM clientes
                       WHERE cliente = '1042'
                         AND disponibilidade < '$vartestevalor'
                     ";
    $rsqlxmlcliente = mysql_query($sqlxmlcliente) or die("Banco XML não abre!");
    while ($rowxmlcliente = mysql_fetch_array($rsqlxmlcliente))
        {
        $contarperfilclientecontar = $contarperfilclientecontar + 1;
        }
    }

My intention is that the table IMOVEIS check all the properties with the field VVENDA who have values MENORES that the countryside DISPONIBILIDADE of the client table and list the number of records found.

Only the numbers don’t match!

Can someone help me in this mystery?

Answer 1

I think you can kill everything in one query only:

SELECT imoveis.id
      ,imoveis.vvenda
  FROM imoveis
  LEFT JOIN clientes
    ON clientes.cliente = imoveis.cod
 WHERE imoveis.cod = '1042'
   AND imoveis.status = '2'
   AND clientes.disponibilidade >= imoveis.vvenda

Answer 2

Using the @Nilsonuehara query, only to "list the number of records found"

SELECT imoveis.id,
       imoveis.vvenda,
       count(*) total
  FROM imoveis
  LEFT JOIN clientes
    ON clientes.cliente = imoveis.cod
 WHERE imoveis.cod = '1042'
   AND imoveis.status = '2'
   AND clientes.disponibilidade >= imoveis.vvenda
 GROUP BY imoveis.id,
          imoveis.vvenda