How to join one select with another when the first one not always returns a value for specific row?

Question

I have a complex query to retrieve some results:

EDITED QUERY (added the UNION ALL):

SELECT  t.*
FROM (

    SELECT  
        dbo.Intervencao.INT_Processo, analista, 
        ETS.ETS_Sigla, ATC.ATC_Sigla, PAT.PAT_Sigla, dbo.Assunto.SNT_Peso,
        CASE 
            WHEN ETS.ETS_Sigla = 'PE' AND (PAT.PAT_Sigla = 'LIB' OR PAT.PAT_Sigla = 'LBR') THEN (0.3*SNT_Peso) 
            WHEN ETS.ETS_Sigla = 'CD' THEN (0.3*SNT_Peso)*0.3  
            ELSE SNT_Peso
        END AS PESOAREA,
        CASE 
            WHEN a.max_TEA_FimTarefa IS NULL THEN a.max_TEA_InicioTarefa
            ELSE a.max_TEA_FimTarefa
        END AS DATA_INICIO_TERMINO,
        ROW_NUMBER() OVER (PARTITION BY ATC.ATC_Sigla, a.SRV_Id ORDER BY TEA_FimTarefa DESC) AS seqnum 
    FROM dbo.Tarefa AS t

    INNER JOIN (
        SELECT 
            MAX(dbo.TarefaEtapaAreaTecnica.TEA_InicioTarefa) AS max_TEA_InicioTarefa,
            MAX (dbo.TarefaEtapaAreaTecnica.TEA_FimTarefa) AS max_TEA_FimTarefa,
            dbo.Pessoa.PFJ_Descri AS analista, dbo.AreaTecnica.ATC_Id, dbo.Tarefa.SRV_Id
        FROM dbo.TarefaEtapaAreaTecnica 
        LEFT JOIN dbo.Tarefa ON dbo.TarefaEtapaAreaTecnica.TRF_Id = dbo.Tarefa.TRF_Id
        LEFT JOIN dbo.AreaTecnica ON dbo.TarefaEtapaAreaTecnica.ATC_Id = dbo.AreaTecnica.ATC_Id 
        LEFT JOIN dbo.ServicoAreaTecnica ON dbo.TarefaEtapaAreaTecnica.ATC_Id = dbo.ServicoAreaTecnica.ATC_Id 
            AND dbo.Tarefa.SRV_Id = dbo.ServicoAreaTecnica.SRV_Id
        INNER JOIN dbo.Pessoa ON dbo.Pessoa.PFJ_Id = dbo.ServicoAreaTecnica.PFJ_Id_Analista
        GROUP BY dbo.AreaTecnica.ATC_Id, dbo.Tarefa.SRV_Id, dbo.Pessoa.PFJ_Descri
    ) AS a ON t.SRV_Id = a.SRV_Id

    INNER JOIN dbo.TarefaEtapaAreaTecnica AS TarefaEtapaAreaTecnica_1 ON 
        t.TRF_Id = TarefaEtapaAreaTecnica_1.TRF_Id 
        AND a.ATC_Id = TarefaEtapaAreaTecnica_1.ATC_Id 
        AND a.max_TEA_InicioTarefa = TarefaEtapaAreaTecnica_1.TEA_InicioTarefa
    LEFT JOIN AreaTecnica ATC ON TarefaEtapaAreaTecnica_1.ATC_Id = ATC.ATC_Id
    LEFT JOIN Etapa ETS ON TarefaEtapaAreaTecnica_1.ETS_Id = ETS.ETS_Id
    LEFT JOIN ParecerTipo PAT ON TarefaEtapaAreaTecnica_1.PAT_Id = PAT.PAT_Id
    LEFT OUTER JOIN dbo.Servico ON a.SRV_Id = dbo.Servico.SRV_Id
    INNER JOIN dbo.Intervencao ON dbo.Servico.INT_Id = dbo.Intervencao.INT_Id
    LEFT JOIN dbo.Assunto ON dbo.Servico.SNT_Id = dbo.Assunto.SNT_Id

) t

The result is following:

It works good, the problem is that I was asked that if when a row is not present on this query, it must contain values from another table (ServicoAreaTecnica), so I got this query for the other table based on crucial information of the first query. So if I UNION ALL I get this:

Query1 + 

UNION ALL

     SELECT INN.INT_Processo, 
            PES.PFJ_Descri,
            NULL, --ETS.ETS_Sigla, 
            ART.ATC_Sigla, 
            NULL ,--PAT.PAT_Sigla,
            ASS.SNT_Peso,
            NULL, --PESOAREA
            NULL, --DATA_INICIO_TERMINO
            NULL --seqnum

     FROM dbo.ServicoAreaTecnica AS SAT
     INNER JOIN dbo.AreaTecnica AS ART ON ART.ATC_Id = SAT.ATC_Id
     INNER JOIN dbo.Servico AS SER ON SER.SRV_Id = SAT.SRV_Id
     INNER JOIN dbo.Assunto AS ASS ON ASS.SNT_Id = SER.SNT_Id
     INNER JOIN dbo.Intervencao AS INN ON INN.INT_Id = SER.INT_Id
     INNER JOIN dbo.Pessoa AS PES ON PES.PFJ_Id = SAT.PFJ_Id_Analista

The result is following:

So what I want to do is to remove row number 1 because row number 2 exists on the first query, I think I got it explained better this time. The result should be only row number 1, row number 2 would appear only if query 1 doesn't retrieve a row for that particular INN.INT_Processo.

Thanks!

Answer 1

Ok, there are two ways to reduce your record set. Given that you've already written the code to produce the table with the extra rows, it might be easiest to just add code to reduce that:

Select * from 
    (Select *
      , Row_Number() over 
           (partition by IntProcesso, Analista order by ISNULL(seqnum, 0) desc) as RN
    from MyResults) a
where RN = 1

This will assign row_number 1 to any rows that came from your first query, or to any rows from the second query that do not have matches in the first query, then filter out extra rows.

You could also use outer joins with isnull or coalesce, as others have suggested. Something like this:

Select ISNULL(a.IntProcesso, b.IntProcesso) as IntProcesso
    , ISNULL(a.Analista, b.Analista) as Analista
    , ISNULL(a.ETSsigla, b.ETSsigla) as ETSsigla
    [repeat for the rest of your columns]
from Table1 a
full outer join Table2 b
on a.IntProcesso = b.IntProcesso and a.Analista = b.Analista

Answer 2

If you do something like ... Query1 Right Join Query2 On ... that should get only the rows in Query2 that don't appear in Query 1.

Answer 3

Your code is hard to read, because of the lengthy names of everything (and to be honest, the fact that they're in a language I don't speak also makes it a lot harder).

But how about: replacing your INNER JOINs with LEFT JOINs, adding more LEFT JOINs to draw in the alternative tables, and introducing ISNULL clauses for each variable you want in the results?