Início / dba / Perguntas / 40657
Accepted
Kahn
Asked: 2013-04-23 23:21:23 +0800 CST

Obtendo o nome da coluna que foi usada em uma junção?

  • 772

Esta pode ser uma pergunta um pouco estranha:

O modelo de dados do nosso projeto tem várias tabelas com várias colunas, qualquer uma das quais pode ser usada para se juntar a uma tabela de configuração. A questão aqui é como obter o nome da coluna que foi usada para se conectar à tabela de configuração em uma junção OR?

Por exemplo, digamos que a consulta tenha esta aparência:

SELECT FIRST.ID, SEC.ID, TRD.ID, FRTH.ID, SETS.ID, SETS.VALUE
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECDONTABLE_ID = FIRST.SECONDTABLE_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRDTABLE_ID = FIRST.THIRDTABLE_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTHTABLE_ID = TRD.FOURTHTABLE_ID
-- etc...
LEFT JOIN SETTINGTABLE SETS 
   ON (SETS.ID = FIRST.ONESETTING_ID OR SETS.ID = FIRST.SECONDSETTING_ID
      OR SETS.ID = FIRST.THIRDSETTING_ID OR SETS.ID = TRD.RANDSETTING_ID
      OR SETS.ID = FRTH.ONESETTING_ID
-- etc...
   )

Agora, esse é um exemplo muito analisado do que realmente está acontecendo. Podemos ter dezenas de tabelas com várias colunas joinin em cada uma delas para o SETTINGTABLE.

Obviamente, simplesmente fazer uma junção separada para cada table.column mostraria a coluna usada. Ou, alternativamente, você pode usar a cláusula CASE no select. Mas qualquer um deles é complicado e requer muita entrada manual. Mesmo apenas usando todas as colunas de ID de junção no select permitiria que você comparasse os IDs, mas como os resultados precisam ser lidos por pessoas não técnicas, isso não ajudaria muito.

Então, existe uma maneira de usar alguns objetos sys ou algo assim para simplesmente retornar o nome da coluna que foi realmente usado para conectar-se a SETTINGTABLE? O design é o que é e não mudará. Então, estou simplesmente tentando encontrar a melhor maneira de trabalhar com o que temos. Isso aceleraria significativamente nosso diagnóstico de alguns de nossos scripts, consultas e dados.

Obrigado!

sql-server join

1 respostas

  1. Best Answer

    Não há nenhum sysobjeto ou DMV que lhe dê tais informações. Você terá que construí-lo sozinho, para o qual você pode dar uma olhada no exemplo abaixo.

    Vamos preparar algumas tabelas e dados fictícios.

    CREATE TABLE ROOTTABLE (FIRST_ID INT, SECOND_ID INT, THIRD_ID INT, ONESETTING_ID INT);
CREATE TABLE SECONDTABLE (SECOND_ID INT, SECONDSETTING_ID INT);
CREATE TABLE THIRDTABLE (THIRD_ID INT, FOURTH_ID INT, RANDSETTING_ID INT, THIRDSETTING_ID INT);
CREATE TABLE FOURTHTABLE (FOURTH_ID INT, ONESETTING_ID INT);
CREATE TABLE SETTINGTABLE (ID INT, VALUE Varchar(10));

INSERT ROOTTABLE VALUES (1,2,3,15),(1,1,1,12),(11,5,6,12);
INSERT SECONDTABLE VALUES (1,11),(2,15),(5,13);
INSERT THIRDTABLE VALUES (1,5,6,15),(6,7,8,15),(3,7,8,15);
INSERT FOURTHTABLE VALUES (5,15),(6,16),(7,17),(1,18);
INSERT SETTINGTABLE VALUES
  (11,'one'),
  (12,'two'),
  (13,'three'),
  (14,'four'),
  (15,'five'),
  (16,'six'),
  (17,'seven');

    Aqui está a consulta com a coluna MATCHES para mostrar de onde a condição JOIN foi atendida (SETS.ID = ?).

    SELECT FIRST.FIRST_ID, SEC.SECOND_ID, TRD.THIRD_ID, FRTH.FOURTH_ID, SETS.ID, SETS.VALUE,
       STUFF((SELECT ','+SOURCE_TABLE
              FROM (VALUES
                    ('ROOTTABLE',   FIRST.ONESETTING_ID),  -- 1
                    ('SECONDTABLE', SEC.SECONDSETTING_ID), -- 2
                    ('THIRDTABLE',  TRD.THIRDSETTING_ID),  -- 3
                    ('THIRDTABLE',  TRD.RANDSETTING_ID),   -- 4
                    ('FOURTHTABLE', FRTH.ONESETTING_ID))   -- 5
                   X (SOURCE_TABLE, SOURCE_ID)
              WHERE X.SOURCE_ID = SETS.ID
              FOR XML PATH('')),1,1,'') MATCHES
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECOND_ID = FIRST.SECOND_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRD_ID = FIRST.THIRD_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTH_ID = TRD.FOURTH_ID
LEFT JOIN SETTINGTABLE SETS 
     ON (SETS.ID = FIRST.ONESETTING_ID    -- 1
      OR SETS.ID = SEC.SECONDSETTING_ID   -- 2
      OR SETS.ID = TRD.THIRDSETTING_ID    -- 3
      OR SETS.ID = TRD.RANDSETTING_ID     -- 4
      OR SETS.ID = FRTH.ONESETTING_ID);   -- 5

    Observe que coloquei as fontes que poderiam fazer a correspondência (1-5) no VALUESconstrutor de linhas e nas ONcláusulas para que você possa seguir o padrão.

    Resultados:

    | FIRST_ID | SECOND_ID | THIRD_ID | FOURTH_ID | ID | VALUE |                          MATCHES |
-----------------------------------------------------------------------------------------------
|        1 |         2 |        3 |         7 | 15 |  five | ROOTTABLE,SECONDTABLE,THIRDTABLE |
|        1 |         2 |        3 |         7 | 17 | seven |                      FOURTHTABLE |
|        1 |         1 |        1 |         5 | 11 |   one |                      SECONDTABLE |
|        1 |         1 |        1 |         5 | 12 |   two |                        ROOTTABLE |
|        1 |         1 |        1 |         5 | 15 |  five |           THIRDTABLE,FOURTHTABLE |
|       11 |         5 |        6 |         7 | 12 |   two |                        ROOTTABLE |
|       11 |         5 |        6 |         7 | 13 | three |                      SECONDTABLE |
|       11 |         5 |        6 |         7 | 15 |  five |                       THIRDTABLE |
|       11 |         5 |        6 |         7 | 17 | seven |                      FOURTHTABLE |

    Algumas coisas a serem observadas:

    1. Os múltiplos ORs no JOIN significam que SETS.IDpodem ser correspondidos por qualquer uma das fontes - a consulta mostra todos eles separados por vírgulas.
    2. As linhas 3-5 com as colunas (1, 1, 1, 5)iniciais correspondem a SETS.ID três vezes.

    SQL Fiddle

    Para maior clareza, este outro SQL Fiddle mostra as *SETTING_IDcolunas adicionais usadas para corresponder ao SETS.ID.

    • 2

relate perguntas