Mysql query problem with empty column

Question

Mysql query problem with empty column

Navigation

#1 by (2 votes)

2

I have a Query where I give a select with INNER JOIN between two tables (contacts and company) I have a field called business_id where I search the business table to find the business name, but the business_id field is not required, so you have some contacts with the business_id = 0 field, you could solve this with two SELECT , but I do not think it's the best way. I have seen in some cases the use of IF and ELSE in the query, but I have no mastery of this use. the query so far that I am using with error, it only displays the companies that have the id_enterprise 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 business_id is 0, return the empty or null company name.

mysql

asked by anonymous 29.11.2017 / 13:39

1 answer

How to join multiple commits from the same file? How to remove duplicate elements from a list in C?

score 2 · Accepted Answer

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

LEFT JOIN:

Returns all records in the left table (table A) and matches that exist 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