Sql using left join

Question

Sql using left join

Navigation

#1 by (1 votes)

1

I have the following tables:

TB_ESTOQUE
|PRODUTO|QT_DISPONIVEL|COD_FILIAL|
|0856322|      5      |    41    |
|0856351|      2      |    41    |
|0856322|      9      |    114   |
|0856720|      3      |    20    |

TB_FILIAL
|COD_FILIAL|COD_LOCALADM| 
|    41    |    114     |
|    128   |    114     |
|    133   |    114     |
|    10    |    156     |
|    56    |     12     |

Desired result:

|COD_FILIAL|  ESTOQUE_FILIAL  |
|    41    |     41 - 2       |
|    128   |128 - SEM ESTOQUE |
|    133   |133 - SEM ESTOQUE |

I'm trying to do SQL in this way:

SELECT
F.COD_FILIAL,
CASE WHEN E.QT_DISPONIVEL IS NULL THEN
  F.COD_FILIAL || ' - ' || 'SEM ESTOQUE'
ELSE F.COD_FILIAL || ' - ' || E.QT_DISPONIVEL
END AS ESTOQUE_FILIAL
FROM
TB_FILIAL F
LEFT JOIN TB_ESTOQUE E
ON E.COD_FILIAL = F.COD_FILIAL AND  E.COD_PRODUTO = 0856351 AND
F.COD_LOCALADM = 114
ORDER BY
E.COD_FILIAL

SQL is returning all records from the TB_ESTOQUE table. Ignoring the command

"F.COD_LOCALADM = 114"

sql firebird inner-join

asked by anonymous 08.10.2018 / 21:57

1 answer

POST disabled in JBOSS EAP 7 Bug using after pseudo-element on small screens

score 1 · Accepted Answer

The ON no LEFT JOIN works for the keys you want to keep in your JOIN , to add conditions you need a WHERE , as in:

SELECT
F.COD_FILIAL,
CASE WHEN E.QT_DISPONIVEL IS NULL THEN
F.COD_FILIAL || ' - ' || 'SEM ESTOQUE'
ELSE F.COD_FILIAL || ' - ' || E.QT_DISPONIVEL
END AS ESTOQUE_FILIAL
FROM
TB_FILIAL F
LEFT JOIN TB_ESTOQUE E
ON E.COD_FILIAL = F.COD_FILIAL 
WHERE E.COD_PRODUTO = 0856351 AND
F.COD_LOCALADM = 114
ORDER BY
E.COD_FILIAL