How to show the result of one query based on the result of another?

Navigation
1

Query A: 

   SELECT V.CHAPA AS CHAPA,
       F.NOME AS NOME,
       V.DATA AS DATA,
       DATEDIFF(mi, MIN(BATIDA), MAX(BATIDA)) AS DIFF
FROM V_BATIDASANTIGAS AS V
LEFT JOIN V_DADOSFUNC AS F ON V.CHAPA = F.CHAPA
WHERE V.BATIDA IS NOT NULL
  AND SEQUENCIALBATIDA IN (2,
                           3)
  AND MONTH (V.DATA) = 9
GROUP BY V.CHAPA,
         V.DATA,
         F.NOME
HAVING DATEDIFF(mi, MIN(BATIDA), MAX(BATIDA)) < 60
AND DATEDIFF(mi, MIN(BATIDA), MAX(BATIDA)) > 0
ORDER BY DATA DESC

Inquiry B: 

SELECT V.CHAPA AS CHAPA,
       F.NOME AS NOME,
       V.DATA AS DATA,
       V.CODCOLIGADA
FROM V_BATIDASANTIGAS AS V
LEFT JOIN V_DADOSFUNC AS F ON V.CHAPA = F.CHAPA
WHERE V.BATIDA IS NOT NULL
  AND V.CODCOLIGADA = 1
GROUP BY V.CHAPA,
         V.DATA,
         F.NOME,
         V.CODCOLIGADA
HAVING (MAX(V.SEQUENCIALBATIDA) > 4
        OR MAX(V.SEQUENCIALBATIDA) = 1)
ORDER BY DATA DESC

I need to display the result of Query A disregarding the Query B records.

An example would be something like: 

IF (Relsultado_Consulta_A == Relsultado_Consulta_B) 
BEGIN DECARTAR RESULTADOS IGUAIS 
END 
ELSE 
BEGIN MOSTRA RESULTADOS QUE EXISTEM APENAS NA CONSULTA A 
END

How could I do it?

    
asked by anonymous 19.10.2016 / 18:10

2 answers

2

You can use SELECT DISTINCT with UNION between the two queries 

SELECT DISTINCT * 
FROM
(
  SELECT * FROM CONSULTA_A
  UNION
  SELECT * FROM CONSULTA_B
) CONSULTAS

The UNION will join the results of the two tables. And the DISTINCT will only bring the non-repeated results.

EDIT

After what we talked about in the comments, this might resolve: 

SELECT A.CHAPA,
    A.NOME,
    A.DATA,
    A.DIFF,
    B.CODCOLIGADA

FROM
(
    SELECT V.CHAPA AS CHAPA,
           F.NOME AS NOME,
           V.DATA AS DATA,
           DATEDIFF(mi, MIN(BATIDA), MAX(BATIDA)) AS DIFF
    FROM V_BATIDASANTIGAS AS V
    LEFT JOIN V_DADOSFUNC AS F ON V.CHAPA = F.CHAPA
    WHERE V.BATIDA IS NOT NULL
      AND SEQUENCIALBATIDA IN (2,
                               3)
      AND MONTH (V.DATA) = 9
    GROUP BY V.CHAPA,
             V.DATA,
             F.NOME
    HAVING DATEDIFF(mi, MIN(BATIDA), MAX(BATIDA)) < 60
    AND DATEDIFF(mi, MIN(BATIDA), MAX(BATIDA)) > 0
) A
LEFT OUTER JOIN
(
    SELECT V.CHAPA AS CHAPA,
           F.NOME AS NOME,
           V.DATA AS DATA,
           V.CODCOLIGADA
    FROM V_BATIDASANTIGAS AS V
    LEFT JOIN V_DADOSFUNC AS F ON V.CHAPA = F.CHAPA
    WHERE V.BATIDA IS NOT NULL
      AND V.CODCOLIGADA = 1
    GROUP BY V.CHAPA,
             V.DATA,
             F.NOME,
             V.CODCOLIGADA
    HAVING (MAX(V.SEQUENCIALBATIDA) > 4
            OR MAX(V.SEQUENCIALBATIDA) = 1)
) B
ON A.CHAPA = B.CHAPA
AND A.NOME = B.NOME
AND A.DATA = B.DATA
ORDER BY A.DATA DESC
    
19.10.2016 / 18:28
1

You can use Union all , but in the second query use not exists comparing the two results. 

SELECT A.CHAPA
      ,A.NOME
      ,A.DATA
      ,A.DIFF
      ,CODE = NULL
FROM #TMP_A A   -- seria a consulta A
UNION ALL
SELECT B.CHAPA
      ,B.NOME
      ,B.DATA
      ,DIFF = NULL
      ,B.CODE
FROM (SELECT CHAPA = B.CHAPA
            ,NOME = B.NOME
            ,DATA = B.DATA
            ,CODE = B.CODE
     FROM #TMP_B B  -- Seria a consulta b
     WHERE NOT EXISTS (SELECT 1
                       FROM #TMP_A bA
                       WHERE bA.CHAPA = B.CHAPA)
) B
GO

The first select will return all records of #TMP_A , and in the second select you return from #TMP_B all items that do not exist in #TMP_A .

    
19.10.2016 / 19:06
How to set data correctly in Lisview? avoid error log with mysql connection