Can a zero be placed in a query if there is no equality when comparing two fields in mysql?

Question

Can a zero be placed in a query if there is no equality when comparing two fields in mysql?

Navigation

#1 by (0 votes)

1

I have two tables in a database (in Mysql) both tables have different fields, one of which has a field called cuenta and costo , and the other one a field called numero :

Tabla uno
cuenta      |   costo

Y,

Tabla dos
numero

From table two I must perform a concatenation to obtain a field called referencia

select concat('1','-45',numero,'-12')referencia from dos

I compare the field cuenta and referencia of each of the tables and if the fields are the same then print the field costo .

The query is as follows:

select u.costo
from dos d LEFT JOIN uno u ON concat('1','-45',d.numero,'-12')=u.cuenta

So in my query you should show me 3 columns; the concatenated fields, the number field and finally the query that shows the equality and what I want is for the fields to remain empty or with 0 in those fields where there are no equalities.

mysql

asked by Madaley 13.07.2018 в 23:23

source

1 answer

Two submit to Edit and Insert in the same Form I need to print from my executed program as a Windows service

score 0 · Accepted Answer

All the columns you need in the final result of the query should be placed in SELECT .

And, to change the value of NULL to 0 when there are no matches in LEFT JOIN you can use COALESCE .

So, you can write your query like this:

SELECT
    u.costo, 
    CONCAT('1','-45',d.numero,'-12') referencia, 
    COALESCE(u.cuenta,'0') cuenta 
FROM dos d 
LEFT JOIN uno u ON CONCAT('1','-45',d.numero,'-12')=u.cuenta;

NOTE: Do not use CONCAT if there is a possibility that in some cases any of the columns participating in the concatenation can be NULL since in those cases CONCAT sets everything to NULL . Yes you want more details about this you can consult the question: Use CONCAT or CONCAT_WS in MySQL? and your response.