Mysql Select Empty Cells not showing [WHERE part]

Question

I'm doing a software for payment control as a freelance, but I have a few issues about mysql. I have a table that works as a log, called "Bitacora". In the table I have a column named idCliente, that it is the id of a person in its own table, because I have another table named "Cliente" with a column named "idCliente" so when i do the select I expect to show the name of the client.

The example below shows how the table "Bitacora" works with 3 rows.

idBitacora, tipoBitacora, **idCliente**, idPago, idPagoVario, fecha, razon

'1', '1', **'6',** '-1', '-1', '2017-05-06 21:52:53', ''

'2', '1', **'7',** '-1', '-1', '2017-05-06 21:53:01', ''

'3', '1', **'-1',** '-1', '-1', '2017-05-06 22:19:33', ''

So I made a select query

> SELECT **IF(bitacora.tipoBitacora = 1, 'Cliente Agregado',
> IF(bitacora.tipoBitacora = 2, 'Cliente Borrado',
> IF(bitacora.tipoBitacora = 3, 'Cliente Editado',
> IF(bitacora.tipoBitacora = 4, 'Pago Mensual', IF(bitacora.tipoBitacora
> = 5, 'Pago Vario', IF(bitacora.tipoBitacora = 6, 'Intento Clave Fallida', 'Desconocido')))))) AS Suceso** , IF(bitacora.idCliente =
> -1, '', cliente.idCliente) as idCliente FROM bitacora, cliente where cliente.idCliente=bitacora.idCliente ;

The bold above its a conditional to know what kind of log it is.

> SELECT IF(bitacora.tipoBitacora = 1, 'Cliente Agregado',
> IF(bitacora.tipoBitacora = 2, 'Cliente Borrado',
> IF(bitacora.tipoBitacora = 3, 'Cliente Editado',
> IF(bitacora.tipoBitacora = 4, 'Pago Mensual', IF(bitacora.tipoBitacora
> = 5, 'Pago Vario', IF(bitacora.tipoBitacora = 6, 'Intento Clave Fallida', 'Desconocido')))))) AS Suceso , **IF(bitacora.idCliente =
> -1, '', cliente.idCliente) as idCliente** FROM bitacora, cliente where cliente.idCliente=bitacora.idCliente ;

And the bold above its a conditional I made to make sure that if there is no client in cliente.idCliente the row can still show up, but in my results the one with the bitacora.idCliente that doesnt appear in Cliente (-1) doesnt show, so with the example of the first table shown, the query only return

> Cliente Agregado |Mark
> Cliente Agregado |Zack

But I want to get

> Cliente Agregado |Mark
> 
> Cliente Agregado |Zack
> 
> Cliente Agregado |(nothing)

And I have tried, but still can't get what I want, I think its because the WHERE part. But still cant figure it out, after days, I think a need some help please.

score 1 · Accepted Answer · answered Jul 04 '17 at 07:06

SELECT IF(bitacora.tipoBitacora = 1, 'Cliente Agregado', IF(bitacora.tipoBitacora = 2, 'Cliente Borrado', IF(bitacora.tipoBitacora = 3, 'Cliente Editado', IF(bitacora.tipoBitacora = 4, 'Pago Mensual', IF(bitacora.tipoBitacora = 5, 'Pago Vario', IF(bitacora.tipoBitacora = 6, 'Intento Clave Fallida', 'Desconocido')))))) AS Suceso , IF(bitacora.idCliente = -1, '', cliente.idCliente) as idCliente 
FROM bitacora
LEFT JOIN cliente ON cliente.idCliente=bitacora.idCliente;

You should use like above because If we join tables with comma separated then INNER JOIN will be applied. Try as above! Hope this will help you.

For well understanding visit this link - SQL left join vs multiple tables on FROM line?

score 0 · Answer 2 · answered Jul 04 '17 at 05:13

You're using the wrong construct. You should be using case… else. That doesn't mean you can't continue as you are, but you'd be better with:

case
  when bitacora.tipoBitacora = 2 then 'Cliente Borrado'
  when bitacora.tipoBitacora = 3 then 'Cliente Editado'
  else 'nothing'
end as `my_column`

score 0 · Answer 3 · answered Jul 04 '17 at 05:15

You can use this query 
SELECT
(
CASE
when bitacora.tipoBitacora = 1 THEN 'Cliente Agregado'
when bitacora.tipoBitacora = 2 THEN 'Cliente Borrado'
when bitacora.tipoBitacora = 3 THEN 'Cliente Editado'
when bitacora.tipoBitacora = 4 THEN 'Pago Mensual'
when bitacora.tipoBitacora = 5 THEN 'Pago Vario'
when bitacora.tipoBitacora = 6 THEN 'Intento Clave Fallida' 
ELSE 'Desconocido'
END
)AS Suceso,
(
CASE
WHEN bitacora.idCliente = -1 THEN cliente.idCliente
ELSE 'nothing'
END
) AS idCliente
FROM bitacora, cliente where cliente.idCliente=bitacora.idCliente ;

Mysql Select Empty Cells not showing [WHERE part]

3 Answers3