SELECT

''As RefNo,

'' AS ERPItmDesc,

'' as TDesc,

'' as Item Description,

'' as Date,

0 as ID

FROM TBLDETAIL FM INNER JOIN TBLHDRDETAIL MD

ON MD.CID=FM.CID AND MD.ID=FM.MRPID LEFT OUTER JOIN TBLHDR MH

ON MD.BRNID=MH.BRNID AND MD.CID=MH.CID AND MD.HDRID=MH.ID AND MH.ITMID=FM.ITEMID

FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID

WHERE

FM.CID=1200 AND FM.YMONTH=201207 AND FM.MONTHSECTION=1

UNION

(SELECT

TENDERREFNO as RefNo,

I.ITNAME AS ERPItmDesc,

MH.TDESC as TDesc,

MH.Item Description,

FM.Date,

MH.ID

FROM TBLDETAIL FM INNER JOIN TBLHDRDETAIL MD

ON MD.CID=FM.CID AND MD.ID=FM.MRPID LEFT OUTER JOIN TBLHDR MH

ON MD.CID=MH.CID AND MD.HDRID=MH.ID AND MH.ITMID=FM.ITEMID

LEFT OUTER JOIN STITMMAS I

ON MH.ITMID=I.ITMID LEFT OUTER JOIN TBLMAS U

ON I.ITMUNITID=U.UNITID

FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID

WHERE FM.CID=1200 AND FM.YMONTH=201207 AND FM.MONTHSECTION=1

)