Full Outer Join

Question

Full Outer Join

rajashreesql2014

Newbie

Points: 7
More actions
January 13, 2014 at 9:15 am

#302635

Hi ,
My current output after using a full outer join is :
Year2013Year2014Branch2013Branch2014
10 0 Civil Null
0 5 Null Mechnical
0 50 Null Civil
4 4 Civil Civil
How can I see all of the Civil data together
Like
Year2013Year2014Branch2013Branch2014
14 54 Civil Civil
Note : I tried sum but since the type is varchar .. I am getting an error.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

rajashreesql2014 (1/13/2014)
Hi ,
My current output after using a full outer join is :
Year2013Year2014Branch2013Branch2014
10 0 Civil Null
0 5 Null Mechnical
0 50 Null Civil
4 4 Civil Civil
How can I see all of the Civil data together
Like
Year2013Year2014Branch2013Branch2014
14 54 Civil Civil
Note : I tried sum but since the type is varchar .. I am getting an error.

I'm not sure what you are trying to do. First please read the article in my signature on how to post these types of questions along with DDL and sample data. Second, why is 2014 = 54 and not 59? Why Civil for Branch2014 instead of Mechnical or Mechnical, Civil?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

What about giving us the complete scenario? You say this is a result of a FULL JOIN, but you might not need it if it's a self join.

Please post sample data and the original query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

rajashreesql2014 (1/13/2014)
Note : I tried sum but since the type is varchar .. I am getting an error.

Did you try using MAX? That works with VARCHARs.

I agree that you might find more value in our responses if you post up the original data and query where you think you need a FULL JOIN, as there might be better ways to get the final result.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St