0

I have a query that should return me all users out of the special area. If i had areas, the result still the same

SELECT users.id AS id, SUBSTRING(users.zipcode, 1, 2) 
FROM users JOIN country ON users.country_id = country.country_id 
WHERE country.country_code NOT IN ('AD','AF','BE')
OR (country.country_code = 'FR' AND SUBSTRING(users.zipcode, 1, 2)
NOT IN (69,38,26,07,42))

id | user_zipcode | country_code

1 | 38 | FR

1 | 69 | BE

I don't want all users with country_code 'AD','AF','BE' but if country_code is "FR" i don't want only those with the user_zipcode's 69,38,26,07,42

pelijojo
  • 23
  • 8
  • 1
    Can you provide sample data and your desired results? That would help us understand what you are trying to do. – SS_DBA Jun 07 '17 at 13:20
  • I think 'FR' needs to be included in the 1st not in. Since it has special additional conditions to consider. It seems like you want all users not in countries AD, AF, BE and FR provided the zip code is in (69,38,26,07,42). if correct, then FR needs to be in the 1st not in. also consider not in doesn't work very well with NULL values. If – xQbert Jun 07 '17 at 13:26
  • Countries: Andorra, Afghanistan and Belgium French departments: Rhone, Isere, Drome, Ardeche and Loire. Please explain what you want to happen here. – Stavr00 Jun 07 '17 at 14:11

1 Answers1

0

Exclude Andora, Afghanistan, Belgium and some French departments:

WHERE country.country_code NOT IN ('AD','AF','BE')
AND NOT (
    country.country_code = 'FR' AND
    SUBSTRING(users.zipcode, 1, 2) IN (69,38,26,07,42)
)
Stavr00
  • 3,219
  • 1
  • 16
  • 28