/*
--	CopyRight(C) 2003，NTT All rights reserved
--
--  XMS2データベースビューの作成
--
--	スクリプト概要

--		XMS2データベースのビューを新規に作成します

--
--	変更履歴
--		2004.01.08 新規作成 箕輪英司
--
*/

CREATE VIEW v_LOList 
AS 
SELECT 
   c.LOID AS ParentID,
   c.LOTitle AS ParentName,
   c.foreignID AS ParentForeignID,
   l.RealLOID AS RealID,
   l.LOType AS Type,
   l.LOID AS ChildID,
   l.LOTitle AS ChildName,
   l.foreignID AS ChildForeignID, 
   r.sort_no AS sort_no 
 FROM 
   LORelation r, 
   LO l, 
   LO c 
 WHERE 
   l.LOID = r.childID 
  AND 
   c.LOID = r.parentID 
  AND 
   c.LOType = '0' 
;

CREATE VIEW v_UserList AS 
SELECT 
   g.group_id AS ParentID,
   g.group_name AS ParentName,
   g.foreignID AS ParentForeignID,
   p.user_id AS ChildID,
   p.user_name AS ChildName,
   p.foreignID AS ChildForeignID,
   r.sort_no AS sort_no 
 FROM 
   GroupAndPeople r, 
   People p, 
   Groups g 
 WHERE 
   p.user_id = r.childID 
  AND 
   g.group_id = r.parentID 
;