Home / Questions / 240758
Accepted
Antonio Ruiz
Asked: 2020-02-26 03:35:18 +0800 CST 2020-02-26 03:35:18 +0800 CST

Left Join between two tables in MySQL

  • 772

I try to do the following:

  SELECT a.id_serie, a.serie_num, IFNULL(d.total,0)

  FROM ( SUBCONSULTA ) d

  LEFT
  JOIN fact_contabilidad a
    ON a.id_serie = d.id_serie
    AND a.serie_num = d.serie_num
  • The subquery is a very, very extensive query of several nested selects where there are many tables involved and I do not show it to make it clearer, I leave an extract of the subquery.
id_serie | serie_num | nombre   | total
2508     | 1         | Cliente1 | 20€
2508     | 2         | Cliente1 | 10€
2508     | 4         | Cliente2 | 20€

Now, I want to do a left join between that previous query and a table where all the series are found, like the following:

id_serie | serie_num | nombre  
2508     | 1         | Cliente1         
2508     | 2         | Cliente1      
2508     | 3         | Cliente2
2508     | 4         | Cliente2
  • The series_num = 3 does not appear in the above.

And the result should be the following:

id_serie | serie_num | nombre   | total
2508     | 1         | Cliente1 | 20€
2508     | 2         | Cliente1 | 10€
2508     | 3         | Cliente1 | 0€
2508     | 4         | Cliente2 | 20€

I want to list all the series and if there is no line in the subquery, mark it with €0.

It's possible? I'm trying but I don't know where I went wrong.

mysql

1 Answers

  1. Best Answer

    So that the result of the "join" has a row for each row of the "fact_accounting" table, you must put this table before the LEFT JOIN:

    SELECT a.id_serie, a.serie_num, IFNULL(d.total, '0€')
FROM fact_contabilidad a
LEFT JOIN ( SUBCONSULTA ) d
    ON a.id_serie = d.id_serie
    AND a.serie_num = d.serie_num

    With the function IFNULL()you can pass a default value in case there is no row in table "d" with which to do the join.

    • 5