How to group by after an outer join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to group by after an outer join

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2013-10-18 : 10:24:29

I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.

My query


select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town

Any idea how to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB

I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 10:52:47

dont you have common field on which you can group on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-18 : 14:06:27

quote:
Originally posted by insanepaul

I've got 2 tables of towns. I'm using outer join because i need all the town from both tables. However I'm sometimes getting duplicates.

My query

select a.town, b.town
from a
outer join b on a.town = b.town
group by a.town, b.town

Any idea how to stop getting null values?
portsmouth null
portsmouth portsmouth
southampton southampton
null southampton
TownA null
null TownB

I'm looking for distinct values like this:
portsmouth portsmouth
southampton southampton
TownA null
null TownB
etc...

Are you using LEFT, RIGHT, or FULL outer join? The syntax doesn't seem to say (which should have generated a syntax error).

In any of those cases, it should not produce the result that you are showing. Can you post the sample data in table a and table b that produced this data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources