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
)
this is the query i am having currently
need to modify it to get the result
SELECT mh.ID, mh.tdesc
FROM TBLHDR mh
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
where mh.cid=1200