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'