-- koza.csv
SELECT id, name, common_code AS koza_code, 
description_pamp, 
shashin1, 
shashin2,
shashin3,
kaisaishu_hyoji_web, kaisaibi_hyoji_web, kaisaijikan_hyoji_web, 
kyouzai_tanka_zeikomi, 
setsubihi_zeikomi, 
curriculum, 
shanai_mochimono,
shanai_ryui,
shanai_henko,
biko_web
FROM smsevent
INNER JOIN smsevent_sub1 ON smsevent.id=smsevent_sub1.id_sub1 
INNER JOIN smsevent_sub2 ON smsevent.id=smsevent_sub2.id_sub2
WHERE smsevent.deleted=0
	AND id IN ('187380', '183094', '183253')
ORDER BY common_code ASC


-- koza_koushi.csv
SELECT event_contacts.event_id as kouza_id, CONCAT(last_name, ' ', IFNULL(first_name, '') ) AS full_name,  
	CONCAT(last_name_furigana, ' ', IFNULL(first_name_furigana, '') ) AS name_furigana, 
	contacts.description as teacher_profile 
FROM event_contacts
INNER JOIN event_contacts_sub1 ON event_contacts.id=event_contacts_sub1.id_sub1
INNER JOIN contacts ON contacts.id=event_contacts.contact_id AND event_contacts.deleted=0 AND contacts.smsgroup='teacher'
INNER JOIN contacts_sub1 ON contacts.id=contacts_sub1.id_sub1 AND contacts_sub1.id_sub1=event_contacts.contact_id
INNER JOIN smsevent ON smsevent.id=event_contacts.event_id AND smsevent.deleted=0 AND smsevent.category='course'
WHERE event_contacts.deleted=0 AND event_contacts.event_contact_status='3' 
	AND event_contacts.event_id IN ('187380', '183094', '183253')
	AND contacts.description IS NOT NULL AND contacts.description!=''
	
	
-- INNER JOIN smsevent_sub1 ON smsevent.id=smsevent_sub1.id_sub1 
-- 	AND smsevent_sub1.nenki LIKE '2013%'

-- tag.csv  テーブルはまだ作成されていません。
SELECT kouza_id, tag_name FROM `kouza_tag` WHERE 1

-- genre.csv
SELECT id, master_name, master_group, master_category1, master_category2, school_id, display_order
FROM `master_kanri` WHERE deleted=0 AND `master_group` LIKE 'pamp_janru%' ORDER BY master_group DESC, display_order, id
