Affiliate members harvest on UA wiki

SQL

# avoid duplicate user names
SELECT DISTINCT uawikimedia_p.pagelinks.pl_title AS "Affiliated Username",
p.page_title AS "Affiliate Groups"

FROM uawikimedia_p.page AS p

JOIN uawikimedia_p.pagelinks ON uawikimedia_p.pagelinks.pl_from = p.page_id

# harvest from the a list of affiliates
WHERE p.page_title IN ( "Члени_організації" )
# exclude anything that ends with _(WMF)
# as these are WMF staffs not aff members
AND uawikimedia_p.pagelinks.pl_title NOT LIKE "%_(WMF)"
# remove MediaWiki_message_delivery as it's
# the mass message delivery default handle
AND uawikimedia_p.pagelinks.pl_title NOT LIKE "%MediaWiki_message_delivery%"
# focus on the User: namespace
AND uawikimedia_p.pagelinks.pl_namespace = 2

# Unite both tables
UNION

# avoid duplicate user names
SELECT DISTINCT uawikimedia_p.iwlinks.iwl_title AS "Affiliated Username",
p.page_title AS "Affiliate Groups"

FROM uawikimedia_p.page AS p

JOIN uawikimedia_p.iwlinks ON uawikimedia_p.iwlinks.iwl_from = p.page_id

# harvest from the a list of affiliates
WHERE p.page_title IN ( "Члени_організації" )
# also exclude interwiki links that are not user names
AND uawikimedia_p.iwlinks.iwl_title NOT LIKE "Користувач:%"
# exclude anything that ends with _(WMF)
# as these are WMF staffs not aff members
AND uawikimedia_p.iwlinks.iwl_title NOT LIKE "%_(WMF)"
# remove MediaWiki_message_delivery as it's
# the mass message delivery default handle
AND uawikimedia_p.iwlinks.iwl_title NOT LIKE "%MediaWiki_message_delivery%"
SQL

Checking query status...
