Sql using left Join

Asked

Viewed 45 times

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 make SQL that 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 me all table records TB_ESTOQUE. Ignoring the command 

"F.COD_LOCALADM = 114"
sql firebird inner-join

  • Remove F.COD_LOCALADM = 114 from LEFT JOIN with table TB_ESTOQUE and put in a WHERE just below.

1 answer

1


The ON in the 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

Browser other questions tagged sql firebird inner-join

You are not signed in. Login or sign up in order to post.