SQL query duplicate rows from union

Question

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 tbluser 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'

a '' row is because i get 2 rows for a PK, meaning i get Apple|'' Apple|realitem — user222427
– user222427, Commented Sep 1, 2011 at 15:00
I'd love to know what the down votes are row, i simply want to know how to get the union to stop treating the column Outside as two unique rows — user222427
– user222427, Commented Sep 1, 2011 at 15:05

Lost in Alabama · Accepted Answer · 2011-09-01 15:33:23Z

1

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

answered Sep 1, 2011 at 15:33

Lost in Alabama

1,65310 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FLOOD racer · Accepted Answer · 2011-09-01 15:28:24Z

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')

Codervj · Accepted Answer · 2011-09-01 14:59:57Z

0

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

answered Sep 1, 2011 at 14:59

Codervj

11 bronze badge

1 Comment

user222427 Over a year ago

I did try to inner join on both, but the union thinks they are two unique rows

Stuart Blackler · Accepted Answer · 2011-09-01 15:08:45Z

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'

Collectives™ on Stack Overflow

SQL query duplicate rows from union

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related