Error getting multiple results from a subquery - SQL

  

Author's response to the question you provided in an edition to the original question.

In the end I used the function that you have told me and I have done it in the following way:

I have 2 queries, in which I have to extract a value for each of a specific table, and then check if the first one has value. In case of having no value, I'll take the second one. My solution (I do not know if it is more or less optimal, but functional) has been to use temporary tables. I have created a temporary table to store the result of the first query, and another temporary table to store the result of the second. Then, in a third table, I use the ISNULL function (valueTable1, valueTable2) to check the result of the two previous tables, being as follows:

I create my main temporary table, where I will store the result of the check:

CREATE TABLE #TABLA_CARGADOR_INFORMESMA (idInformeSMA INT, Cargador varchar(4000))

I get the value of the first query in the first temporary table:

select [InformeSMA].idInformeSMA, [RegModifNRSMA].Responsable CargadorInformeSMA--,
into #TABLA_CARGADOR_INFORMESMA_LOG
    from [InformeSMA]
        left join [RegModifNRSMA] 
        on [InformeSMA].[idInformeSMA] = [RegModifNRSMA].[idSMA] and [RegModifNRSMA].[idAccion] in (20000)--,20005,20013,20014,20015,20016,30000)
        left join [AccionesRegModifCulture] 
        on AccionesRegModifCulture.idAccionesRegModif = [RegModifNRSMA].[idAccion] and AccionesRegModifCulture.idCulture = 1
        left JOIN   Auditoria 
        ON [RegModifNRSMA].idAuditoria = Auditoria.idAuditoria
        where [InformeSMA].[idInformeSMA] 
        in (SELECT ID FROM #IDSENTIDADESeq)

I get the value of the second query in the second temporary table:

 select InformeSMA.idInformeSMA,
(SELECT TOP 1   CASE WHEN Responsable.apellido2 like '%Delegado por%' OR Responsable.apellido2 like '%Simulado por%'

      THEN LTRIM(RTRIM(ISNULL(dbo.Responsable.apellido1, '') +
                                ' ' 
                                + ISNULL(dbo.Responsable.apellido2, '') +
                                ' ' 
                                + ISNULL(dbo.Responsable.nombre, '')))
      ELSE LTRIM(RTRIM(ISNULL(dbo.Responsable.nombre, '') +
                                ' ' 
                                + ISNULL(dbo.Responsable.apellido1, '') +
                                ' ' 
                                + ISNULL(dbo.Responsable.apellido2, '')))
END
                FROM    ResponsableAuditoriaPrivilegio
                LEFT JOIN Responsable ON Responsable.idResponsable = ResponsableAuditoriaPrivilegio.idResponsable
                WHERE   ResponsableAuditoriaPrivilegio.idAuditoriaGama = InformeSMA.idInformeSMA
                        AND 
                        (ResponsableAuditoriaPrivilegio.borrado = 0 OR ResponsableAuditoriaPrivilegio.borrado IS NULL)
                        AND ResponsableAuditoriaPrivilegio.idPrivilegio = 26 --P26 - GESTOR INFORME SMA
                        ) as 'cargadorInforme'

into #TABLA_CARGADOR_INFORMESMA_PRIVILEGIO
from InformeSMA
WHERE InformeSMA.idInformeSMA
 in (SELECT ID FROM #IDSENTIDADESeq)

I use the main table to get the desired result with the ISNULL () function:

    INSERT INTO #TABLA_CARGADOR_INFORMESMA
    select distinct #TABLA_CARGADOR_INFORMESMA_LOG.idInformeSMA,            isnull(#TABLA_CARGADOR_INFORMESMA_LOG.CargadorInformeSMA,   #TABLA_CARGADOR_INFORMESMA_PRIVILEGIO.cargadorInforme)
from #TABLA_CARGADOR_INFORMESMA_PRIVILEGIO
inner join #TABLA_CARGADOR_INFORMESMA_LOG on #TABLA_CARGADOR_INFORMESMA_LOG.idInformeSMA = #TABLA_CARGADOR_INFORMESMA_PRIVILEGIO.idInformeSMA
where #TABLA_CARGADOR_INFORMESMA_LOG.idInformeSMA 
in (SELECT ID FROM #IDSENTIDADESeq)

Finally, if I make a select * of the main temporary table, I get the expected result. Even if you do not understand what each of the querys does, I hope you understand the procedure I followed to obtain my solution and that I can help people.

You do not need to make a subquery at all in this case, a simple JOIN is more than enough:

SELECT A.idAM, ISNULL(A.Responsable,B.Responsable) Responsable
FROM dbo.Tabla1 A
LEFT JOIN dbo.Tabla2 B
    ON A.idAM = B.idAM;

I'm not sure I understand what you want to do, but I would think of something with ISNULL(expresion_a_evaluar, valor_de_reemplazo) . In the first parameter it could be the first sub query and the second parameter your second sub query. And I think it would be two ISNULL nested:

SELECT
    ID,
    ISNULL(
        ISNULL(TU_PRIMER_SUB_CONSULTA, TU_SEGUNDA_SUB_CONSULTA), '') as RESPONSABLE
FROM
    TABLA_DE_DONDE_OBTIENES_EL_ID

That's my idea, if I understood you correctly.

Greetings.