
<!-- #include file="../include.asp" -->

<%

main()

Sub main()

	Dim EnshuCD
	EnshuCD = Request("EnshuCD")
	Dim Query, Content
	Query = "SELECT * FROM TM名刺デザインM ORDER BY 名刺デザインCD "
	
	Query = "INSERT INTO TS会社M"
	Query = Query & " SELECT B.*"
	Query = Query & " FROM (SELECT TS会社M.演習CD, "
	Query = Query & " TS会社M.会社CD + '-' + A.学生番号 AS 会社CD, A.学生番号, "
	Query = Query & " TS会社M.会社名称, TS会社M.会社略名称, TS会社M.会社カナ名称,"
	Query = Query & "  TS会社M.郵便番号, TS会社M.住所1, TS会社M.住所2, "
	Query = Query & " TS会社M.TEL, TS会社M.FAX, TS会社M.業種CD, "
	Query = Query & " TS会社M.自社フラグ, TS会社M.予備あ, TS会社M.予備い, "
	Query = Query & " TS会社M.予備う, TS会社M.予備1, TS会社M.予備2, "
	Query = Query & " TS会社M.予備3"
	Query = Query & " FROM TS会社M CROSS JOIN"
	Query = Query & " (SELECT TS学生M.学生番号"
	Query = Query & " FROM TS学生M INNER JOIN"
	Query = Query & " TS学生グループM ON "
	Query = Query & " TS学生M.学生グループCD = TS学生グループM.学生グループCD"
	Query = Query & " INNER JOIN"
	Query = Query & " TS演習許可学生グループM ON "
	Query = Query & " TS学生グループM.学生グループCD = TS演習許可学生グループM.学生グループCD"
	Query = Query & " INNER JOIN"
	Query = Query & " TS演習M ON "
	Query = Query & " TS演習許可学生グループM.演習CD = TS演習M.演習CD"
	Query = Query & " INNER JOIN"
	Query = Query & " TS会社M ON "
	Query = Query & " TS演習M.演習CD = TS会社M.演習CD AND "
	Query = Query & " TS学生M.学生番号 <> TS会社M.会社CD"
	Query = Query & " WHERE (TS演習許可学生グループM.演習CD = '" & EnshuCD & "')"
	Query = Query & " GROUP BY TS学生M.学生番号) A"
	Query = Query & " WHERE (TS会社M.演習CD = '" & EnshuCD & "') AND "
	Query = Query & " (TS会社M.学生番号 = '')) B LEFT OUTER JOIN"
	Query = Query & " TS会社M ON B.演習CD = TS会社M.演習CD AND "
	Query = Query & " B.会社CD = TS会社M.会社CD"
	Query = Query & " WHERE (TS会社M.演習CD IS NULL)"

	Set Content = OpenQuery(Query)

Response.Write("完了しました")
End Sub
%>