Interesting and Urgent Join Query Question

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

SQL Server 2005 Forums

Transact-SQL (2005)

Interesting and Urgent Join Query Question

Author

Topic

pinecrest515
Starting Member

11 Posts

Posted - 2011-01-25 : 13:29:23

Dear forumers,

I would like to consult urgently on the following:

Suppose I have two tables, and want to join them:

Table 1:
Company This_Year_Sales
Disney 1000
Pepsi 2000

Table 2:
Company Last_Year_Sales
Coke 3000
Target 1000

I want to join these tables together so that all the information is presented, with three columns: Company, This_Year_Sales, Last_Year_Sales, like this:

Company This_Year_Sales Last_Year_Sales
Disney 1000 n/a
Pepsi 2000 n/a
Coke n/a 3000
Target n/a 1000

How do I do it?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-25 : 13:46:11

You can use a FULL OUTER JOIN: http://msdn.microsoft.com/en-us/library/ms187518.aspx

But may want to read this too: http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-25 : 15:45:46

Union All.

SELECT Company, This_Year_Sales, NULL AS Last_Year_Sales FROM Table1
UNION ALL
SELECT Company, NULL AS This_Year_Sales, Last_Year_Sales FROM Table2

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 12:25:40

what if same company appears in this year and last year sales? you want to show them in 1 or 2 rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources