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 ?

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? — D Mac, Oct 30 '13 at 12:57
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 — Dev'Dev, Oct 30 '13 at 13:02

score 23 · Accepted Answer · answered Oct 30 '13 at 13:01

23

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
      )

answered Oct 30 '13 at 13:01

Code Lღver

15,573
16
56
75

Isn't there an "EXCEPT" in MySQL? I believe NOT IN will give you some performance issues – Schalk Oct 30 '13 at 13:32
@Schalk no there is no such thing in mysql – Code Lღver Oct 30 '13 at 13:43

score 0 · Answer 2 · answered Oct 30 '13 at 13:04

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

score 0 · Answer 3 · edited Feb 01 '18 at 03:16

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

SQL Statement - SELECT the inverse of this query

3 Answers3

Linked