SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to group by after an outer join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

insanepaul
Posting Yak Master

178 Posts

Posted - 10/18/2013 :  10:24:29  Show Profile  Reply with Quote
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...

Edited by - insanepaul on 10/18/2013 10:39:46

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2013 :  10:52:47  Show Profile  Reply with Quote
dont you have common field on which you can group on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3699 Posts

Posted - 10/18/2013 :  14:06:27  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000