SQL Statement - SELECT the inverse of this query

Question

I have this query and i want to select the inverse of what that select.

SELECT Guide.id FROM Guide
INNER JOIN GuideAvailability ON Guide.id = GuideAvailability.guideId
WHERE GuideAvailability.startDate IN (1377946800)
GROUP BY GuideAvailability.guideId
HAVING COUNT(DISTINCT GuideAvailability.id) = 1

Is there a easy way to solve my problem ?

Leonidaleonidas

Define "Inverse"? Give examples. – Xantha

It depends on what you mean by the inverse. One answer is change "IN" to "NOT IN". Another answer is to change "HAVING ... = 1" to "HAVING ... <> 1". Or do both. What do you want? – Beaune

This query returns the id of the Guide that correspond to my condition. I want to get all guide excepts the ones that are selected in this query – Benjamin

Answer 1

Simply use this query:

SELECT * 
     FROM Guide where id NOT IN (
       SELECT Guide.id 
         FROM Guide
         INNER JOIN GuideAvailability ON Guide.id = GuideAvailability.guideId
         WHERE GuideAvailability.startDate IN (1377946800) 
         GROUP BY GuideAvailability.guideId 
         HAVING COUNT(DISTINCT GuideAvailability.id) = 1
      )

Compare

Isn't there an "EXCEPT" in MySQL? I believe NOT IN will give you some performance issues – Elasticity

@Elasticity no there is no such thing in mysql – Rapport

Answer 2

MAy be you are looking for this

SELECT Guide.id FROM Guide
INNER JOIN GuideAvailability ON Guide.id = GuideAvailability.guideId
WHERE GuideAvailability.startDate NOT IN (1377946800) GROUP BY GuideAvailability.guideId HAVING COUNT(DISTINCT GuideAvailability.id) <> 1

Ecosphere

Answer 3

You can flip the bool. ;)

I do this when I have a large amount of where filters and there is a specific exclusion set of filters. I write them inside the case and do =1 to exclude and =0 to find what I excluded.

SELECT Guide.id FROM Guide
INNER JOIN GuideAvailability ON Guide.id = GuideAvailability.guideId
WHERE ( case when GuideAvailability.startDate IN (1377946800) then 1 else 0 end) = 0
GROUP BY GuideAvailability.guideId
HAVING COUNT(DISTINCT GuideAvailability.id) = 1

Cry