I have following tables:
base:
id | domain
extended:
id | domain | country_code
countries:
id | country_code | country_name
banned_domains:
id | domain
I will have several thousands (more than 500K) of domains in banned_domains. Now I need to fetch data "domain, country_code and country_name" which do not exist on banned_domains list. I am not so good at the MySQL JOINS, can anyone guide me for the proper query.
SELECT b.domain, ex.country_code, c.country_name FROM base b
INNER JOIN extended ex ON b.domain=ex.domain
INNER JOIN countries c ON ex.country_code=c.country_code
WHERE b.domain NOT IN (SELECT domain FROM banned_domains);
You can use this query .
select b.domain , e.country_code , c.country_name from base b join extended e on b.domain = e.domain join countries c on e.country_code = c.country_code and b.domain not in (select domain from banned_domains);
Try this link http://sqlfiddle.com/#!2/f78ea/1 .
