select 
d.MasterID,
d.PageNum,
d.ValueString,
m.MasterName,
m.PartNumber,
m.IsActive,
de.DeptID,
de.DeptCaption,
p.ProcessID,
p.ProcessCaption, 
l.FormComponentID,
l.FormComponentWidth,
l.FormComponentHeight,
(select ', ' + fpath from filemapping fm where fm.fname = d.ValueString for xml path('')) fpath,
(select ', ' + dd.ValueString + ':' + cast(dd.MasterID as varchar(10)) from DETAIL dd where dd.ValueString = d.ValueString for xml path('')) pics,
(select top 1 lower(fpath) from filemapping fm join DETAIL dd on fm.fname = dd.ValueString where dd.MasterID = m.MasterID group by fpath order by count(*) desc) pathHint
from DETAIL d
join "MASTER" m on d.MasterID = m.MasterID
join DEPT de on d.DeptID = de.DeptID
join PROCESS p on d.DeptID = p.DeptID and d.ProcessID = p.ProcessID
join MASTPROC mp on m.MasterID = mp.MasterID and p.DeptID = mp.DeptID and p.ProcessID = mp.ProcessID
join LAYOUT l on p.LayoutID = l.LayoutId and d.FormComponentId = l.FormComponentId 
--join filemapping fm on d.ValueString = fm.fname
-- where exists (select 1 from filemapping fm where fm.fname = d.ValueString and fm.duplicate = 1)
where exists (select 1 from filemapping fm where fm.fname = d.ValueString)
-- and m.MasterID = 876
and m.MasterID = 1265
order by d.ValueString, d.MasterID

select * from DETAIL d
join filemapping fm on d.ValueString = fm.fname
where d.MasterID = 876
order by d.ValueString


select * from fmap fm1
left join filemapping fm2 on fm1.fname = fm2.fname and fm1.fpath = fm2.fpath


select 
d.ValueString, count(*)
from DETAIL d
join "MASTER" m on d.MasterID = m.MasterID
join PROCESS p on d.DeptID = p.DeptID and d.ProcessID = p.ProcessID
join MASTPROC mp on m.MasterID = mp.MasterID and p.DeptID = mp.DeptID and p.ProcessID = mp.ProcessID
join LAYOUT l on p.LayoutID = l.LayoutId and d.FormComponentId = l.FormComponentId 
where l.FormComponentType in (2, 9, 11, 12)
group by d.ValueString
having count(*) > 2


-----------------------
/*select DeptReleased from mastdept where MasterID=368 and DeptReleased=1
select m.MasterID, m.MasterName from master m, subcomp c where c.ParentID=368 and c.MasterID=m.MasterID
select m.mastername, m.masterid, m.IsReleased from subcomp c, master m where c.masterid=368 and m.masterid=c.parentid
select * from detail where MasterID=368
select * from longtext where LongID=3152 or LongID=11799
select * from mastdept, dept where mastdept.DeptID=dept.DeptID and MasterID=368 order by dept.RecordOrder
select * from Locks where terminalid=19230 and docmasterid=0
select d.masterid, d.deptid, c.code, c.description, c.RevNum, c.RevDate from docmastdept d, contents c where d.deptid=15 and c.masterid=d.masterid order by 3, 4
select * from mastproc where MasterID=368 and DeptID=15  order by recordorder
select * from mastdept where MasterID=368 and DeptID=15

select * from layout where LayoutID=88 order by recordorder
select * from fields where DeptID=15 and ProcessID=7
select * from detailpart where partnumber='0170-4639'
select * from srcfields where SourceID=1 order by fieldname
select * from detailpart2 where partnumber='0170-4639'
select d.RecordOrder as DeptOrder, s.RecordOrder, s.MasterID, s.SOPCaption, s.SOPIcon, s.IsReleased, s.RevNum, s.RevDate, s.DeptID, s.LayoutID, d.DeptID, s.ProcessID
  from sop s, dept d
  where s.DeptID=d.DeptID
  and s.DeptID=6
  order by 1, 2
*/