Mysql problem with empty column query

Asked

Viewed 162 times

2

I have a Query where I give a select with INNER JOIN between two tables (contacts and company), I have a field called id_company where I search in the companies table to find the company name, but the id_company field is not mandatory, so it has some contacts with the id_company = 0 field, could solve this with two SELECT, but I don’t think it’s the best way. I saw in some cases the use of IF and ELSE in the query, but I do not have control of this use. the query so far that I am using with error, poís displays only companies that have the id_company field other than 0 (empty):

SELECT c.nome,c.telefone1,c.email,e.nome AS empresa
FROM contatos AS c JOIN empresas AS e WHERE  c.id_empresa = e.id

I think of a single query solution, where when the id_company is 0, return the empty or null company name.

mysql

  • he has no company. in the form it is not mandatory to enter a company.

  • See if my answer meets you, I made an online example also to facilitate.

  • Possible duplicate of Left Join or Not Exists

1 answer

2


You can return all contacts regardless of the existence of a company using LEFT JOIN:

LEFT JOIN:

Returns all records from the left table (table A) and any matches with the right table (table B).

SELECT contatos.nome
   , contatos.telefone1
   , contatos.email
   , empresas.nome AS empresa 
FROM contatos 
LEFT JOIN empresas 
   ON contatos.id_empresa = empresas.id;

Sqlfiddle - See it working online

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.