[Resolvido] Preciso de algo similar ao JOIN

[DiegoAngra07 21]

Dae gente,

 

Acho que estou com um probleminha nas mãos... É um desafio, eu já rodei o google todo e não achei algo que ajudasse hehe é o seguinte:

 

Supondo que tenho as 4 tabelas a seguir (todos os dados são fictícios, desculpem não sou criativo para exemplos):

 

           PRINCIPAL
--------------------------------
ID_P | DESCRICAO | TIPO | ID_FK <- Uma chave estrangeira que pode ser qualquer um dos IDs das 2 tabelas a seguir.
 1    TESTE       NULL   NULL
 2    TESTE B       Y      1
 3    TESTE C       Z      2
 4    TESTE D       Z      1

O campo TIPO indica se a relação será com a tabela 1 ou 2. Exemplo: SE TIPO = 'Z' ID_FK será ligado com ID_T1.

 

         TABELA_1
-------------------------------
ID_T1 | DESCRICAO | ID_TABELA_3 <- Chave estrangeira de ligação com a TABELA 3, é simples não é problema.
 1     BRASIL          80
 2     CHILE           81
 3     EUA             82

             TABELA_2
----------------------------------
ID_T2 | DESCRICAO    | ID_TABELA_3
 1     SAO PAULO          82
 2     PORTO ALEGRE       84
 3     MANAUS             90

       TABELA_3
-----------------------
ID_TABELA_3 | DESCRICAO
    80	      VERDE
    81	      AZUL
    82	      AMARELO
    84	      LARANJA
    86	      ROXO
    90	      PRETO

Bom, funciona assim:

 

O campo ID_FK na tabela PRINCIPAL pode ser tanto NULO quanto ligado às outras 2 TABELAS. Ou seja um LEFT JOIN TABELA_X ON PRINCIPAL.ID_FK = TABELA_X.ID_TX

 

Eu preciso buscar os dados da PRINCIPAL, os dados da tabela ligada ao registro corrente na busca e os dados da TABELA 3 ligados à tabela corrente.

 

Porém, não posso usar o LEFT JOIN pois ele está me retornando resultados "mesclados". Com LEFT JOIN a query fica assim:

 

SELECT P.ID_P, P.DESCRICAO, T1.DESCRICAO, T2.DESCRICAO, TA.DESCRICAO, TB.DESCRICAO FROM PRINCIPAL AS P
LEFT JOIN TABELA_1 AS T1 ON T1.ID_T1 = P.ID_FK -- Junção da tabela 1 com a principal
LEFT JOIN TABELA_2 AS T2 ON T2.ID_T2 = P.ID_FK -- Junção da tabela 2 com a principal
LEFT JOIN TABELA_3 AS TA ON TA.ID_TABELA_3 = T1.ID_TABELA_3 -- Junção da tabela 1 com a tabela 3
LEFT JOIN TABELA_3 AS TB ON TB.ID_TABELA_3 = T2.ID_TABELA_3 -- Junção da tabela 2 com a tabela 3

O que ela me retornaria:

 

ID_P | P.DESCRICAO | T1.DESCRICAO | T2.DESCRICAO | TA.DESCRICAO | TB.DESCRICAO
------------------------------------------------------------------------------
 1  | TESTE       |              |              |              |
 2  | TESTE B	   | BRASIL	  | SAO PAULO	 | VERDE        | AMARELO
 3  | TESTE C	   | CHILE	  | PORTO ALEGRE | AZUL	        | LARANJA
 4  | TESTE D	   | BRASIL	  | SAO PAULO	 | VERDE        | AMARELO

Mas na verdade o que eu preciso é só os dados da tabela ligada, usando o campo TIPO pra identificar isso. Eu já consegui buscar uma descrição só, assim:

 

SELECT P.ID_P, P.DESCRICAO, 
(SELECT T1.DESCRICAO FROM TABELA_1 AS T1 WHERE T1.ID_T1 = P.ID_FK AND P.TIPO = 'Z'), 
(SELECT T2.DESCRICAO FROM TABELA_2 AS T2 WHERE T2.ID_T2 = P.ID_FK AND P.TIPO = 'Y')
FROM PRINCIPAL AS P

Retorno:

 

ID_P | P.DESCRICAO | T1.DESCRICAO | T2.DESCRICAO
------------------------------------------------
 1  | TESTE       |              |            
 2  | TESTE B	   |    	  | SAO PAULO	
 3  | TESTE C	   | CHILE	  | 
 4  | TESTE D	   | BRASIL	  | 

Até aqui tá ok, mas nas subquerys de SELECT ali eu tentei incluir os dados da TABELA_3 que preciso relacionar, além de que tenho ouros campos nas tabelas 1 e 2 além do DESCRICAO citado nesse exemplo para buscar. No caso eu tentei fazer isso:

 

SELECT P.ID_P, P.DESCRICAO, 
(SELECT T1.DESCRICAO, TA.DESCRICAO FROM TABELA_1 AS T1 JOIN TABELA_3 AS TA ON TA.ID_TABELA_3 = T1.ID_TABELA_3 WHERE T1.ID_T1 = P.ID_FK AND P.TIPO = 'Z'), 
(SELECT T2.DESCRICAO, TB.DESCRICAO FROM TABELA_2 AS T2 JOIN TABELA_3 AS TB ON TB.ID_TABELA_3 = T2.ID_TABELA_3 WHERE T2.ID_T2 = P.ID_FK AND P.TIPO = 'Y')
FROM PRINCIPAL AS P

Qual seria o resultado esperado:

 

ID_P | P.DESCRICAO | T1.DESCRICAO | T2.DESCRICAO | TA.DESCRICAO | TB.DESCRICAO
------------------------------------------------------------------------------
 1  | TESTE       |              |              |              |
 2  | TESTE B	   |    	  | SAO PAULO	 | 	        | AMARELO
 3  | TESTE C	   | CHILE	  |              | AZUL	        | 
 4  | TESTE D	   | BRASIL	  |              | VERDE        | 

Mas estas subquerys aparentemente não podem retornar mais que 1 coluna quando não estão no WHERE...

 

Pra mim a solução é simples: Unir as colunas da subquery com a da principal, assim como o JOIN faz. Não entendo muito de VIEW e não sei se uma poderia resolver o problema aqui, e mesmo assim não posso utilizar uma neste caso por motivos que não vem ao caso. Nem uma PROCEDURE. Mesmo assim, se uma das duas resolverem o problema fico grato se alguém postar, pelo menos só para que eu saiba e veja a solução dessa história.

 

Espero que tenha sido claro, qualquer ajuda é bem-vinda, fico grato desde já.

[Henrique Barcelos 290]

O campo TIPO indica se a relação será com a tabela 1 ou 2. Exemplo: SE TIPO = 'Z' ID_FK será ligado com ID_T1.

Ruim, ruim... :P

 

Você deveria criar 2 tabelas de relacionamento ao invés de fazer isso.

[DiegoAngra07 21]

Ruim, ruim... :P

 

Você deveria criar 2 tabelas de relacionamento ao invés de fazer isso.

 

Infelizmente também não é viável pro meu caso :/

 

Será que não tem uma saída mesmo?

 

É porque assim, essa tabela principal digamos que seja uma tabela de PAGAMENTO. Nesta tabela você tem registros de pagamentos feitos. No campo TIPO ele guardaria como foi feito o pagamento (vamos usar como exemplo: Cheque, Depósito em conta e A vista). Quando for do tipo "Cheque" o campo ID_FK estará preenchido com o ID do cheque cadastrado na sua respectiva tabela, e a mesma estará relacionada com o banco ao qual o cheque pertence (Tabela 3). Quando for do tipo "Débito em conta" o campo ID_FK vai guardar o ID da conta em que foi feita o pagamento (como eu disse, sou péssimo com exemplos) e a mesma estará também relacionada com um banco. Já quando for A vista, não possui relacionamento. É um exemplo que ilustra bem a situação. No caso a relação entre PRINCIPAL e TABELA_1 ou TABELA_2 é de 1 para 1, não sei se haveria a necessidade de uma tabela de relacionamento.

 

Ah e esqueci de uma coisa, não são só 2 tipos, são 5 :s Mas só usei 2 para o exemplo.

[Motta 645]

Esta modelagem com 2 chaves estrangeiras gera este tipo de problema.

 

Pensou num VIEW que uniria as duas tabelas de tipagem o join seria mais simples, talvez a performence nem tanto.

 

Mas, publique o problema real, fica mais fácil pensar uma solução que num exemplo.

[DiegoAngra07 21]

Ok. Postarei apenas os campos que tem relevância para nós e poucos registros.

 

A tabela principal é uma tabela onde cada registro é um DÉBITO ou um CRÉDITO. Tabela DEBCRE.

 

             DEBCRE
-----------------------------------
ID_DC | DEBCRE | TIPO_DOC | ID_DOC
-----------------------------------
 1   |    D   |     A    |   4
 2   |    C   |     B    |   2
 3   |    C   |     C    |   3
 4   |    C   |    NULL  |  NULL
 5   |    D   |     D    |   1

ID_DC: ID do débito / crédito

DEBCRE: Apenas indica se é um débito ou crédito.

TIPO_DOC: Tipo de documento vinculado ao déb./cré. - Pode ser NULL. Vamos adotar que: A = ORDEM_SER, B = NOTA_FIS, C = NOTA_DEB e D = NOTA_VEN.

ID_DOC : ID do documento vinculado caso TIPO_DOC <> NULL.

 

Tabela nota de débito:

 

          NOTA_DEB
------------------------------
ID_N_DEB | NUMNOTA | ID_FILIAL 
  3     |   105   |    3

ID_N_DEB: ID da nota débito.

NUMNOTA: Número da nota.

ID_FILIAL: Filial a qual a nota pertence.

 

Tabela de nota fiscal:

 

                  NOTA_FIS
-------------------------------------------------
ID_N_FIS | NUMNOTA | SERIE | SUBSERIE | ID_FILIAL 
  2     |   123   |   1   |     1    |    2

ID_N_FIS: ID da nota fiscal.

NUMNOTA: Número da nota.

SERIE: Série da nota.

SUBSERIE: Subsérie da nota. Aceita NULL.

ID_FILIAL: Filial a qual a nota pertence.

 

Tabela de nota de venda:

 

             NOTA_VEN
--------------------------------------
ID_N_VEN | NUMNOTA | SERIE | ID_FILIAL 
  1     |   114   |   1   |    2

ID_N_VEN: ID da nota de venda.

NUMNOTA: Número da nota.

SERIE: Série da nota.

ID_FILIAL: Filial a qual a nota pertence.

 

Tabela de ordem de serviço:

 

         ORDEM_SER
-----------------------------
ID_O_SER | NUM_OS | ID_FILIAL 
  4     |   151  |    1

ID_O_SER: ID da ordem de serviço.

NUM_OS: Número da ordem de serviço.

ID_FILIAL: Filial a qual a ordem pertence.

 

Tabela de filial:

 

     FILIAL
-----------------
ID_FILIAL | SIGLA
   1     |  SP
   2     |  RJ
   3     |  AC

ID_FILIAL: ID da filial.

SIGLA: Sigla da filial.

 

Bom, então temos a seguinte situação:

O registro 1 é um débito , vinculado à uma ordem de serviço de ID 4. O número dessa OS é 151, e a mesma pertence a filial SP.

O registro 2 é um crédito, vinculado à uma nota fiscal de ID 2. O número dessa NF é 123, série 1, subsérie 1, e a mesma pertence a filial RJ.

O registro 3 é um crédito, vinculado à uma nota de débito de ID 3. O número dessa NB é 105, e a mesma pertence a filial AC.

O registro 4 é um crédito, e não possui documento vinculado.

O registro 5 é um débito , vinculado à uma nota de venda de ID 1. O número dessa NV é 114, e a mesma pertence a filial RJ.

 

A query está da seguinte maneira, além de outros campos da tabela DEBCRE que estou buscando também mas eles não interferem neste problema:

 

SELECT DC.ID_DC, DC.DEBCRE,
   -- Buscando o número da nota e a filial a qual pertence a nota débito
   -- vinculada ao registro corrente na busca (DC.ID_DOC) caso o TIPO_DOC seja o da nota débito.
   (SELECT ND.NUMNOTA, FND.SIGLA FROM NOTA_DEB AS ND 
    JOIN FILIAL AS FND ON FND.ID_FILIAL = ND.ID_FILIAL 
    WHERE ND.ID_N_DEB = DC.ID_DOC AND DC.TIPO_DOC = 'C'),

   -- Buscando o número da nota, série, subsérie e a filial a qual pertence a nota fiscal
   -- vinculada ao registro corrente na busca (DC.ID_DOC) caso o TIPO_DOC seja o da nota fiscal.
   (SELECT NF.NUMNOTA, NF.SERIE, NF.SUBSERIE, FNF.SIGLA FROM NOTA_FIS AS NF 
    JOIN FILIAL AS FNF ON FNF.ID_FILIAL = NF.ID_FILIAL 
    WHERE NF.ID_N_FIS = DC.ID_DOC AND DC.TIPO_DOC = 'B'),

   -- Buscando o número da nota, série e a filial a qual pertence a nota de venda
   -- vinculada ao registro corrente na busca (DC.ID_DOC) caso o TIPO_DOC seja o da nota de venda.
   (SELECT NV.NUMNOTA, NV.SERIE, FNV.SIGLA FROM NOTA_VEN AS NV 
    JOIN FILIAL AS FNV ON FNV.ID_FILIAL = NV.ID_FILIAL 
    WHERE NV.ID_N_VEN = DC.ID_DOC AND DC.TIPO_DOC = 'D'),

   -- Buscando o número da ordem e a filial a qual pertence a ordem de serviço
   -- vinculada ao registro corrente na busca (DC.ID_DOC) caso o TIPO_DOC seja o da ordem de serviço.
   (SELECT OS.NUM_OS, FOS.SIGLA FROM ORDEM_SER AS OS 
    JOIN FILIAL AS FOS ON FOS.ID_FILIAL = OS.ID_FILIAL 
    WHERE OS.ID_O_SER = DC.ID_DOC AND DC.TIPO_DOC = 'A')
FROM DEBCRE AS DC

Desculpem se tiver algum erro só fui copiando os blocos e alterando os nomes dos campos. Enfim, é isto. Como disse a query está dando erro pois as subquerys não podem retornar mais que uma coluna. Eu já pensei duas saídas mas é muita gambiarra, queria ver se não tem uma maneira certinha de fazer a coisa.

 

Grato aos que estão ajudando, fico no aguardo =]

[Motta 645]

Como falei talvez juntar as 4 sub-queries (,NOTA_DEB , NOTA_FIS AS NF , FROM NOTA_VEN , ORDEM_SER )

nume view, dando um jeito de tornar as colunas iguais pois não são.

Pelo menos isto tornaria a query mais simples, para ser rápida seriam outros 500 reais.

O que complica ainda mais a coisa é que existem deb/cred não vinculados a documentos, o que requer um outer join.

Eu tentaria uma solução com VIEW.

[DiegoAngra07 21]

Então, acabou que achei a solução quando tava nem pensando nisso. Só colocar o TIPO_DOC dentro do ON dos LEFT JOIN. Bem simples eu acho até que já tinha passado por algo parecido. Segue a modificação na query do primeiro exemplo que passei:

 

SELECT P.ID_P, P.DESCRICAO, T1.DESCRICAO, T2.DESCRICAO, TA.DESCRICAO, TB.DESCRICAO FROM PRINCIPAL AS P
LEFT JOIN TABELA_1 AS T1 ON T1.ID_T1 = P.ID_FK AND P.TIPO = 'Z' -- Junção da tabela 1 com a principal -- Como podem ver eu adicionei o P.TIPO
LEFT JOIN TABELA_2 AS T2 ON T2.ID_T2 = P.ID_FK AND P.TIPO = 'Y' -- Junção da tabela 2 com a principal -- Como podem ver eu adicionei o P.TIPO
LEFT JOIN TABELA_3 AS TA ON TA.ID_TABELA_3 = T1.ID_TABELA_3 -- Junção da tabela 1 com a tabela 3
LEFT JOIN TABELA_3 AS TB ON TB.ID_TABELA_3 = T2.ID_TABELA_3 -- Junção da tabela 2 com a tabela 3

Isso me retorna exatamente o que quero, resultado esperado que postei antes:

 

ID_P | P.DESCRICAO | T1.DESCRICAO | T2.DESCRICAO | TA.DESCRICAO | TB.DESCRICAO
------------------------------------------------------------------------------
 1  | TESTE       |              |              |              |
 2  | TESTE B     |              | SAO PAULO    |              | AMARELO
 3  | TESTE C     | CHILE        |              | AZUL         | 
 4  | TESTE D     | BRASIL       |              | VERDE        | 

Agora comparando com o segundo exemplo.

 

Antes possuía as subquerys:

 

...
-- Buscando o número da nota e a filial a qual pertence a nota débito
-- vinculada ao registro corrente na busca (DC.ID_DOC) caso o TIPO_DOC seja o da nota débito.
(SELECT ND.NUMNOTA, FND.SIGLA FROM NOTA_DEB AS ND 
JOIN FILIAL AS FND ON FND.ID_FILIAL = ND.ID_FILIAL 
WHERE ND.ID_N_DEB = DC.ID_DOC AND DC.TIPO_DOC = 'C')
...

-- Agora é só tirar o WHERE e transformar o FROM de cada uma num LEFT JOIN. Assim:

LEFT JOIN NOTA_DEB AS ND ON ND.ID_N_DEB = DC.ID_DOC AND DC.TIPO_DOC = 'C'
LEFT JOIN FILIAL AS FND ON FND.ID_FILIAL = ND.ID_FILIAL 

Observem que o que era:

 

FROM NOTA_DEB AS ND JOIN FILIAL AS FND ON FND.ID_FILIAL = ND.ID_FILIAL WHERE ND.ID_N_DEB = DC.ID_DOC AND DC.TIPO_DOC = 'C'

 

Se tornou:

 

LEFT JOIN NOTA_DEB AS ND ON ND.ID_N_DEB = DC.ID_DOC AND DC.TIPO_DOC = 'C'

LEFT JOIN FILIAL AS FND ON FND.ID_FILIAL = ND.ID_FILIAL

 

Tudo ficaria assim:

 

SELECT DC.ID_DC, DC.DEBCRE, -- Dados principais
ND.NUMNOTA, FND.SIGLA,      -- Dados da primeira subquery
NF.NUMNOTA, NF.SERIE, NF.SUBSERIE, FNF.SIGLA, -- Dados da segunda subquery
NV.NUMNOTA, NV.SERIE, FNV.SIGLA, -- Dados da terceira subquery
OS.NUM_OS, FOS.SIGLA -- Dados da quarta subquery

-- Junção da tabela NOTA_DEB com o registro corrente caso o TIPO_DOC dele seja C
LEFT JOIN NOTA_DEB AS ND ON ND.ID_N_DEB = DC.ID_DOC AND DC.TIPO_DOC = 'C'
LEFT JOIN FILIAL AS FND ON FND.ID_FILIAL = ND.ID_FILIAL 

-- Junção da tabela NOTA_FIS com o registro corrente caso o TIPO_DOC dele seja B
LEFT JOIN NOTA_FIS AS NF ON NF.ID_N_FIS = DC.ID_DOC AND DC.TIPO_DOC = 'B'
LEFT JOIN FILIAL AS FNF ON FNF.ID_FILIAL = NF.ID_FILIAL 

-- Junção da tabela NOTA_VEN com o registro corrente caso o TIPO_DOC dele seja D
LEFT JOIN NOTA_VEN AS NV ON NV.ID_N_VEN = DC.ID_DOC AND DC.TIPO_DOC = 'D'
LEFT JOIN FILIAL AS FNV ON FNV.ID_FILIAL = NV.ID_FILIAL 

-- Junção da tabela ORDEM_SER com o registro corrente caso o TIPO_DOC dele seja A
LEFT JOIN ORDEM_SER AS OS ON OS.ID_O_SER = DC.ID_DOC AND DC.TIPO_DOC = 'A'
LEFT JOIN FILIAL AS FOS ON FOS.ID_FILIAL = OS.ID_FILIAL 

FROM DEBCRE AS DC

Com os JOIN desta maneira dá de associar correto o registro corrente de acordo com o tipo dele, e trazer somente os dados referentes ao documento correto.

 

Bom, espero que esta solução sirva pra alguém que passe por isso algum dia :P

 

Agradeço à vocês pela atenção, parabéns pelo fórum já me salvou algumas vezes procurando alguns tópicos por aí.

 

Ah e 500 reais tá caro demais :/

 

Abraços, valeu.