ads.txt sql

##italy
select t.`demand_source_id`, t.`line` from rabota_db.`demand_source_txt` t
join rabota_db.`demand_source` ds on ds.`demand_source_id`=t.`demand_source_id`
where t.`demand_source_id` not in (
	select t.demand_source_id FROM rabota_db.`demand_source_requirement` t
	WHERE t.`requirement`="commercial_region" AND t.requirement_type="keep_only" AND t.value="cis"
)
and t.`demand_source_id` not in (23,64)
and ds.`disabled`=0
group by 2
;

##cis
SELECT t.`demand_source_id`, t.`line` FROM rabota_db.`demand_source_txt` t
JOIN rabota_db.`demand_source` ds ON ds.`demand_source_id`=t.`demand_source_id`
WHERE t.`demand_source_id` NOT IN (
	SELECT t.demand_source_id FROM rabota_db.`demand_source_requirement` t
	WHERE t.`requirement`="commercial_region" AND t.requirement_type="keep_only" AND t.value="italy"
)
AND t.`demand_source_id` NOT IN (23,64)
AND ds.`disabled`=0
GROUP BY 2
;

#not italy, not cis
SELECT t.`demand_source_id`, t.`line` FROM rabota_db.`demand_source_txt` t
JOIN rabota_db.`demand_source` ds ON ds.`demand_source_id`=t.`demand_source_id`
WHERE t.`demand_source_id` NOT IN (
	SELECT t.demand_source_id FROM rabota_db.`demand_source_requirement` t
	WHERE t.`requirement`="commercial_region" AND t.requirement_type="keep_only" AND t.value="italy"
)
and t.`demand_source_id` NOT IN (
	SELECT t.demand_source_id FROM rabota_db.`demand_source_requirement` t
	WHERE t.`requirement`="commercial_region" AND t.requirement_type="keep_only" AND t.value="cis"
)
AND t.`demand_source_id` NOT IN (23,64)
AND ds.`disabled`=0
GROUP BY 2
;