Remove duplicate rows from SQL UNION Query

Question

I have the following union query that I am trying to remove duplicate rows from:

SELECT DISTINCT c.Cat_Name, s.SubCat_DisplayName, st.State, COUNT(a.SubCategory_ID) as SubCount
FROM CT_SubCategory s
INNER JOIN CT_Category c ON s.Cat_ID=c.Cat_ID
INNER JOIN CT_AdvertiserSubcategory a ON s.Subcat_ID = a.SubCategory_ID
INNER JOIN CT_AdvertiserCity ac ON ac.UserID = a.UserID
INNER JOIN CT_City ci ON ci.City_ID = ac.City_ID
INNER JOIN CT_State st ON ci.State_ID = st.State_ID
WHERE ci.CityName LIKE 'Grand-Rapids' AND st.Abbr = 'MI'
GROUP BY c.Cat_Name, s.SubCat_Name, s.SubCat_DisplayName, st.State
UNION
SELECT c.Cat_Name, s.SubCat_Name, s.SubCat_DisplayName, null, null
FROM CT_SubCategory s
INNER JOIN CT_Category C ON s.Cat_ID = c.Cat_ID
ORDER BY SubCat_DisplayName

Current Results:

Auto, Auto Repair, null, null
Auto, Auto Repair, Michigan, 3
Entertainment, Events, null, null
Gifts, Flowers, null, null

Desired Results:

Auto, Auto Repair, Michigan, 3
Entertainment, Events, null, null
Gifts, Flowers, null, null

I'd like to remove the duplicate Auto Repair row with the nulls from my result set.

Is this possible to do with a union query?

you'll have to dedup after the union statement, but if you want to remove rows with NULL values, you do that in your WHERE clause — Beth
– Beth, Commented Jan 24, 2015 at 0:18
you have to show the expected result in compare with the current result you are getting which you have shown above, your expect result is not clear for us — void
– void, Commented Jan 24, 2015 at 0:22

void · Accepted Answer · 2015-01-24 00:52:20Z

2

as I understood the question, you don't need UNION at all you have to use LEFT OUTER JOIN;

SELECT DISTINCT c.Cat_Name, s.SubCat_DisplayName, st.State, COUNT(a.SubCategory_ID) as SubCount
FROM CT_SubCategory s
INNER JOIN CT_Category c ON s.Cat_ID=c.Cat_ID
LEFT OUTER JOIN CT_AdvertiserSubcategory a ON s.Subcat_ID = a.SubCategory_ID
LEFT OUTER JOIN CT_AdvertiserCity ac ON ac.UserID = a.UserID
LEFT OUTER JOIN CT_City ci ON ci.City_ID = ac.City_ID
LEFT OUTER JOIN CT_State st ON ci.State_ID = st.State_ID
WHERE ISNULL(ci.CityName,'Grand-Rapids') LIKE 'Grand-Rapids' AND ISNULL(st.Abbr,'MI') = 'MI'
GROUP BY c.Cat_Name, s.SubCat_Name, s.SubCat_DisplayName, st.State

and the result should be like this:

Auto, Auto Repair, Michigan, 3
Entertainment, Events, null, null
Gifts, Flowers, null, null

edited Jan 24, 2015 at 0:52

answered Jan 24, 2015 at 0:29

void

7,9503 gold badges29 silver badges47 bronze badges

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

5 Comments

Gordon Linoff Over a year ago

Your where clause undoes the left join.

void Over a year ago

@GordonLinoff, answer edited, also you can put the where clause outside of the query(make as subquery)

Sebas Over a year ago

AND st.Abbr = 'MI' still undoes the outer join of st. It has to be in the join condition.

void Over a year ago

@Sebas answer edited, but I think the best way in this case is to bring the the where clause outside the query like select * from (select tbla.a as a, tblb.b as b from tbla left outer join tblb on tbla.id=tblb.id) where b='some value'

void Over a year ago

also I think the idea of using left join is important not the detail of query which the question owner can manipulate it

Gordon Linoff · Accepted Answer · 2015-01-24 07:56:21Z

0

Farhang's answer is ok. But the right way to write the query is to put the where condition in the on clause:

SELECT DISTINCT c.Cat_Name, s.SubCat_DisplayName, st.State,
       COUNT(a.SubCategory_ID) as SubCount
FROM CT_SubCategory s INNER JOIN
     CT_Category c
     ON s.Cat_ID = c.Cat_ID LEFT OUTER JOIN
     CT_AdvertiserSubcategory a
     ON s.Subcat_ID = a.SubCategory_ID LEFT OUTER JOIN
     CT_AdvertiserCity ac
     ON ac.UserID = a.UserID LEFT OUTER JOIN
     CT_City ci
     ON ci.City_ID = ac.City_ID AND
        ci.CityName = 'Grand-Rapids' LEFT OUTER JOIN
     CT_State st
     ON ci.State_ID = st.State_ID AND
        st.Abbr = 'MI'
GROUP BY c.Cat_Name, s.SubCat_Name, s.SubCat_DisplayName, st.State;

ISNULL() (or as I prefer COALESCE()) returns unexpected results if CityName or Abbr is ever NULL in the data. For filtering on the right-hand table in a left join, you should put the conditions in the on clause.

answered Jan 24, 2015 at 7:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

David Over a year ago

Great answer. I'm using a built in Kentico CMS control so I need to have the where condition as a full statement for it to work with Kentico

Collectives™ on Stack Overflow

Remove duplicate rows from SQL UNION Query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related