CODEWITHSUNDEEP
mysqlsql
CondeGil
CondeGil

Reputation: 97

Mysql query except

I need to make a query like that:

SELECT DISTINCT Carta_ID
FROM Carta_Alarme
INNER JOIN Alarme ON Carta_Alarme.Alarme_ID = Alarme.ID
WHERE (
YEAR( Alarme.dtOcorrencia ) = 2011
) NOT
IN (

SELECT DISTINCT Carta_ID
FROM Carta_Alarme
INNER JOIN Alarme ON Carta_Alarme.Alarme_ID = Alarme.ID
WHERE (
YEAR( Alarme.dtOcorrencia ) = 2012
)
)

The result of first Select is (1, 5, 6). The result of the other select after "NOT IN" is (1,2,3,4,5) but the result of the total query is (1,2,3,4,5) and i need only (6).

So what i need is an EXCEPT between this selects and what i get is an INTERSECT

Upvotes: 0

Views: 167

Answers (3)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

SQL Fiddle

This is effectively a minus set operation

SELECT DISTINCT ca.Carta_ID
FROM Carta_Alarme ca
INNER JOIN Alarme a ON ca.Alarme_ID = a.ID
left join (SELECT DISTINCT Carta_ID
           FROM Carta_Alarme
           INNER JOIN Alarme ON Carta_Alarme.Alarme_ID = Alarme.ID
           WHERE YEAR( Alarme.dtOcorrencia ) = 2012) ca2 on ca2.Carta_id = ca.Carta_id
WHERE YEAR( a.dtOcorrencia ) = 2011
      and ca2.Carta_id is null

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

SELECT DISTINCT Carta_ID
FROM Carta_Alarme
INNER JOIN Alarme ON Carta_Alarme.Alarme_ID = Alarme.ID
WHERE (
YEAR( Alarme.dtOcorrencia ) = 2011
) AND Carta_ID NOT
IN (

SELECT DISTINCT Carta_ID
FROM Carta_Alarme
INNER JOIN Alarme ON Carta_Alarme.Alarme_ID = Alarme.ID
WHERE (
YEAR( Alarme.dtOcorrencia ) = 2012
)
)

Upvotes: 2

juergen d
juergen d

Reputation: 204756

SELECT Carta_ID
FROM Carta_Alarme
INNER JOIN Alarme ON Carta_Alarme.Alarme_ID = Alarme.ID
GROUP BY Carta_ID
HAVING sum(YEAR(Alarme.dtOcorrencia) = 2011) > 0 
   and sum(YEAR(Alarme.dtOcorrencia) = 2012) = 0

Upvotes: 1

Related Questions