Relationship between the tables

Asked

Viewed 97 times

3

I have the following tables:

TB_ESTOQUE
|COD_PRODUTO|QT_DISPONIVEL|COD_FILIAL|
|  0856322  |      5      |    41    |
|  0856351  |      2      |    41    |
|  0856322  |      9      |    114   |
|  0856720  |      3      |    20    |
|  0856322  |      8      |    128   |
|  0856322  |      12     |     35   |
|  0856322  |      4      |     9    |

TB_FILIAL
|COD_FILIAL|COD_LOCALADM| 
|    41    |    114     |
|    128   |    114     |  
|    114   |    114     |
|    10    |    156     |
|    56    |     12     |
|    35    |     12     |
|    9     |     12     |

TB_PRODUTO
|COD_PRODUTO|DESC_PRODUTO|
|  0856322  | CALCARIO   |
|  0856351  | ZINCO      |
|  0856321  | SEMENTE    |
|  0856720  | INSETICIDA |
|  0856752  | STE SOJA   |

Tabela desejada:
|COD_PRODUTO|DESC_PRODUTO|QT_DISPONIVEL|COD_FILIAL|
|  0856322  |   CALCARIO |       5     |    41    |
|  0856322  |   CALCARIO |       9     |    114   |
|  0856322  |   CALCARIO |       8     |    128   |

I am running the following SQL:

SELECT
E.COD_PRODUTO,
P.DESC_PRODUTO,
E.QT_DISPONIVEL,
E.COD_FILIAL
FROM
TB_ESTOQUE E
LEFT JOIN TB_PRODUTO P
ON P.COD_PRODUTO = E.COD_PRODUTO
WHERE
E.COD_PRODUTO = 856322
ORDER BY
P.DESC_PRODUTO

How I relate in SQL the table TB_FILIAL, filtering by code COD_LOCALADM?

Example:

... WHERE
    E.COD_PRODUTO = 856322 AND
    TB_FILIAL.COD_LOCALADM = 114
sql firebird join inner inner-join

3 answers

4

There is no COD_PRODUTO in table TB_ESTOQUE according to your example.

Try:

SELECT
E.PRODUTO,
P.DESC_PRODUTO,
E.QT_DISPONIVEL,
E.COD_FILIAL
FROM
TB_ESTOQUE E, TB_FILIAL F
LEFT JOIN TB_PRODUTO P
ON P.COD_PRODUTO = E.PRODUTO
WHERE
F.COD_FILIAL = E.COD_FILIAL AND
F.COD_LOCALADM = 114 AND
E.PRODUTO = 856322
ORDER BY
P.DESC_PRODUTO
  • 1

    Actually there is, I put it wrong.

  • The column does not exist, the column I wanted was PRODUCT and this yes exists. In case I helped you please mark my answer as the solution.

  • It didn’t help because the column exists. I’ll rephrase my question.

  • The column does not exist, according to what you put as an example table there is no cod_product column but only product column in the stock table. Editing the question to correct an error in the example and saying that it was not helped is amazing. People like you make me leave to comment on this site, ask for help, are helped, respond poorly and still edit the question. Anyway...

1

Just add the branch table in the clause FROM and add the parameters for filtering in WHERE, without too much complication:

SELECT
E.COD_PRODUTO,
P.DESC_PRODUTO,
E.QT_DISPONIVEL,
E.COD_FILIAL
FROM
TB_FILIAL F, 
TB_ESTOQUE E
LEFT JOIN TB_PRODUTO P
ON P.COD_PRODUTO = E.COD_PRODUTO
WHERE
E.COD_PRODUTO = 856322 
AND E.COD_FILIAL = F.COD_FILIAL 
AND F.COD_LOCALADM = 114
ORDER BY
P.DESC_PRODUTO

0

You can use the code below, because I don’t think there will be a scenario where you keep only one branch in stock without product, or a product without an affiliate.

For WHERE to work, just put the junctions, and then go calling Table.Field according to need.

SELECT
  P.COD_PRODUTO,
  P.DESC_PRODUTO,
  E.QT_DISPONIVEL,
  F.COD_FILIAL
FROM TB_ESTOQUE E
INNER JOIN TB_FILIAL F
  ON F.COD_FILIAL = E.COD_FILIAL
INNER JOIN TB_PRODUTO P
  ON P.COD_PRODUTO = E.COD_PRODUTO
WHERE P.COD_PRODUTO = 856322
  AND F.COD_FILIAL = 114
ORDER BY
  P.DESC_PRODUTO

Browser other questions tagged sql firebird join inner inner-join

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