Problem with condition in SQL query

Question

Problem with condition in SQL query

Navigation

#1 by (3 votes)

1

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

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

I already tried to use OR instead of AND , but the result is not what I 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);

php mysql sql

asked by anonymous 29.01.2015 / 10:57

1 answer

Simple click on Jquery does not work PostgreSQL Unilateral Replication

score 3 · Accepted Answer

You will need to use 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 answer .

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_* .