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

Asked

Viewed 785 times

1

Consultation 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

Query 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 Consultation A disregarding the records of Query B.

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 ?

sql query

  • From what I understand you want the values in A who are not in B, right? Is there any particular column, or are they all?

2 answers

2


You can use a SELECT DISTINCT with the UNION between the two consultations

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

The UNION will merge the results of the two tables. And the DISTINCT will bring only the results not repeated.

EDIT

After what we talked about in the comments, maybe this will solve:

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

  • I used, but added the two, but did not disregard the query b

  • Then there are values that are different between query A and query B. You have checked if they are all equal values?

  • In the two queries the only equal fields are: V.PLAQUE AS PLAQUE, F.NAME AS NAME, V.DATE AS DATA, the last field of each query is different, so it will always add up anyway, it would have some other way of doing than with DISTINCT ?

  • Yes, I’ll put together another answer. One minute. Maybe @Willian’s answer will solve!

  • I made another example. You can test?

  • Tested and validated, Thank you !

Show 1 more comment

1

You can use the Union all, but in the second consultation use the 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 from #TMP_A, and in the second select you return from #TMP_B all items that do not exist in #TMP_A.

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.