SQL query duplicate rows from union

What i have is a union query that is returning two rows because of the column Outside. It returns a row as '' in the top half like its suppose to, and in the bottom half it returns the actual field that is found in the join. I basically need to have a '' row if there is nothing in the join. I understand union by nature dedupe, and i understand why this isn't a dedupe, but i'm not sure how to get it so I only have the row in the join else have a ''.

SELECT Me, Ope, Dd,U11, Id3, Legal
       , PAI = (SELECT tbluser.user FROM tbluser WHERE tblmat.PAI = tbluser.userid)
       , Ial = (SELECT tbluser.user FROM tbluse开发者_高级运维r WHERE tblmat.Id3 = tbluser.userid)
       , '' AS Outside, Dagal
FROM tblmat
WHERE MStatus = 'Open'
    AND (mgroup = 'Cas' OR templategroup = 'Sub' OR tmattertemplate = 'Ss')
    AND (Opte <= CONVERT (NCHAR (8), GETDATE (), 112)
    AND Opte >= DateADD (mm, -6, GETDATE ()))
    AND lookup2 IN('NOL','NH','NE')
UNION
SELECT Me, Ope, Dd, U11, Id3, Legal
       , PAI = (SELECT tbluser.user FROM tbluser WHERE tblmat.PAI = tbluser.userid)
       , Ial = (SELECT tbluser.user FROM tbluser WHERE tblmat.Id3 = tbluser.userid)
       , tblCon.ContactName AS Outside, Dagal
FROM    ttblmat
JOIN tblMatRelatedItems ON tblmat.me = tblMatRelatedItems.me
JOIN tblcon ON tblMatRelatedItems.relatedkey = tblcon.contactid
WHERE MStatus = 'Open'
    AND (mgroup = 'Cas' OR templategroup = 'Sub' OR tmattertemplate = 'Ss')
    AND (Opte <= CONVERT (NCHAR (8), GETDATE (), 112)
    AND Opte >= DateADD (mm, -6, GETDATE ()))
    AND lookup2 IN('NOL','NH','NE') 
    AND tblmatterRelateditems.RelatedItem = 'Contact'
    AND tblcontacts.contacttype = 'Managing Partner'

You can try wrapping the whole thing around another query. You didn't specify the database, but the syntax would look something like this:

SELECT    Me, Ope, Dd, U11, Id3, Legal, PAI, Ial, MAX(Outside), Dagal FROM
( your current UNION query)
GROUP BY Me, Ope, Dd, U11, Id3, Legal, PAI, Ial, Dagal 

You can use a single Left outer join and put you the constraints for the join in the in the on clause to accomplish your task

SELECT
    Me,
    Ope,
    Dd,
    U11,
    Id3,
    Legal,
    PAI = (SELECT tbluser.user FROM tbluser WHERE tblmat.PAI = tbluser.userid),
    Ial = (SELECT tbluser.user FROM tbluser WHERE tblmat.Id3 = tbluser.userid),
    isnull(tblCon.ContactName,'') AS Outside,
    Dagal
FROM
    ttblmat
LEFT OUTER JOIN
    tblMatRelatedItems
ON
    tblmat.me = tblMatRelatedItems.me and
    tblmatterRelateditems.RelatedItem = 'Contact'
LEFT OUTER JOIN
    tblcon
ON
    tblMatRelatedItems.relatedkey = tblcon.contactid and
    tblcon.contacttype = 'Managing Partner' 
WHERE
    MStatus = 'Open' AND
    (mgroup = 'Cas' OR templategroup = 'Sub' OR tmattertemplate = 'Ss') AND
    (Opte <= CONVERT (NCHAR (8), GETDATE (), 112) AND
    Opte >= DateADD (mm, -6, GETDATE ())) AND
    lookup2 IN('NOL','NH','NE') 

try using join that would help inner join wont give result if condition in both tables is not matched

have you tried looking at the COALESCE operator?

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Your example might look something like this (completely untested and possibly not going to return the right results. It's a guideline):

SELECT Me, Ope, Dd, U11, Id3, Legal
   , PAI = (SELECT tbluser.user FROM tbluser WHERE tblmat.PAI = tbluser.userid)
   , Ial = (SELECT tbluser.user FROM tbluser WHERE tblmat.Id3 = tbluser.userid)
   , COALESCE(tblCon.ContactName, '') AS Outside, Dagal
FROM    ttblmat
JOIN tblMatRelatedItems ON tblmat.me = tblMatRelatedItems.me
JOIN tblcon ON tblMatRelatedItems.relatedkey = tblcon.contactid
WHERE MStatus = 'Open'
AND (mgroup = 'Cas' OR templategroup = 'Sub' OR tmattertemplate = 'Ss')
AND (Opte <= CONVERT (NCHAR (8), GETDATE (), 112)
AND Opte >= DateADD (mm, -6, GETDATE ()))
AND lookup2 IN('NOL','NH','NE') 
AND tblmatterRelateditems.RelatedItem = 'Contact'
   AND tblcontacts.contacttype = 'Managing Partner'