Problem with condition in SQL query

Question

I have a query that looks for information from two different tables, with two conditions. Apparently it worked well, but when the field a.vendedor is empty, the result is null.

I understand that the result had to be the same, but I wonder if I can (by changing the query) return the other lines of SELECT, excluding the b.nome when a.vendedor=b.codigo is false.

I’ve tried using the OR instead of AND, but the result is not as expected.

Normal result:

[
  {
    descricao: "1",
    cliente: "José Paulo Rodrigues",
    local: "Mesa 1",
    nome: "Armando Azevedo"
  }
]

"Expected" result when a.vendedor is empty (when a.vendedor = b.codigo is not true):

[
  {
    descricao: "1",
    cliente: "José Paulo Rodrigues",
    local: "Mesa 1",
    nome: "0"
  }
]

My code:

$codigo = $_GET['cod'];
$sqlcode = mysql_query("SELECT a.descricao, a.cliente, a.local, b.nome 
                        FROM terminal_cartao a, funcionarionew b 
                        WHERE descricao='$codigo' AND a.vendedor=b.codigo");

while($result=mysql_fetch_object($sqlcode))
{
  $jsonObj[] = $result;
}

$final_res = json_encode($jsonObj);

Answer 1

You will need to use a LEFT JOIN in your query:

SELECT a.descricao, a.cliente, a.local, b.nome 
FROM terminal_cartao a
LEFT JOIN funcionarionew b ON a.vendedor = b.codigo
WHERE descricao='$codigo';

More information about joins in this reply.

Another thing: avoid using mysql_* in your code: these functions have already been discontinued.

Try to use PDO to access the database instead of mysql_*.