20171017SQL

SELECT buyer.*, member.company_name,member.company_name_kana, member.industry_types_id_large,member.industry_types_id_middle,member.industry_types_id_small, large.industry_types_name AS largeName,middle.industry_types_name AS middleName,small.industry_types_name AS smallName,member.company_url,member.shop_name,member.shop_name_kana,member.ec_url, COALESCE(black_buyer_list.flag,0) AS flag
FROM buyer
LEFT JOIN black_buyer_list ON(buyer.buyer_id = black_buyer_list.target_buyer_id AND black_buyer_list.supplier_id = 1 AND black_buyer_list.delete_flag = 0),member
LEFT JOIN industry_types large ON(member.industry_types_id_large = large.industry_types_id AND large.delete_flag=0)
LEFT JOIN industry_types middle ON(member.industry_types_id_middle = middle.industry_types_id AND middle.delete_flag=0)
LEFT JOIN industry_types small ON(member.industry_types_id_small = small.industry_types_id AND small.delete_flag=0)
WHERE buyer.delete_flag=0 AND member.delete_flag=0 AND buyer.member_id = member.member_id AND buyer.buyer_id NOT IN(
SELECT target_buyer_id
FROM deal_relation
WHERE delete_flag=0 AND supplier_id=1 AND deal_relation_status=1) AND buyer.buyer_id NOT IN(
SELECT buyer_id
FROM deal_application
WHERE delete_Flag=0 AND target_supplier_id=1 AND (deal_application_status IN(1,2) OR (deal_application_status=3 AND deal_application_id IN(
SELECT deal_application_id
FROM deal_relation
WHERE delete_flag=0 AND target_buyer_id=buyer.buyer_id AND supplier_id=1 AND deal_relation_status=1)))) AND buyer.buyer_id NOT IN(
SELECT buyer_id
FROM deal_application
WHERE delete_flag=0 AND target_supplier_id=1) AND buyer_id NOT IN(
SELECT buyer_id
FROM buyer
WHERE delete_flag=0 AND member_id IN(
SELECT member_id
FROM supplier
WHERE delete_flag=0 AND supplier_id=1))
ORDER BY buyer_id
LIMIT 3 OFFSET 0