combine 2 tables SQL Server 2005

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)

combine 2 tables SQL Server 2005

Author

Topic

adyf
Starting Member

2 Posts

Posted - 2011-08-30 : 04:59:06

Hi,

please help me to find a solution for situation below: there are 2 tables:

Sales Table

BookName MonthSales LibrarySales BookStoreSales
x 2010.11 5 1
x 2010.12 2 3
y 2010.10 1 2
y 2010.11 3 2
y 2010.12 4 1

Plan Table

BookName MonthPlan LibraryPlan BookStorePlan
x 2010.12 10 3
x 2011.01 10 3
x 2011.02 5 3
y 2010.11 5 3
y 2010.12 5 3
y 2011.01 5 3
y 2011.02 5 3

How can I display the result in such way to have 1 row/BookName/Month, as shown below:

BookName Month LibrarySales BookStoreSales LibraryPlan BookStorePlan
x 2010.11 5 1 NULL NULL
x 2010.12 2 3 10 3
x 2011.01 NULL NULL 10 3
x 2011.02 NULL NULL 5 3
y 2010.10 1 2 NULL NULL
y 2010.11 3 2 5 3
y 2010.12 4 1 5 3
y 2011.01 NULL NULL 5 3
y 2011.02 NULL NULL 5 3

Thank you,

Adrian

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-30 : 05:28:47

Homework?

The solution is to use an OUTER JOIN ( in this case it will need to be A FULL OUTER JOIN ) on the Sales.bookName to Plan.bookName and Sales.MonthSales to Plan.MonthPlan columns.

I'll leave the syntax up to you but it's a really easy query.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

adyf
Starting Member

2 Posts

Posted - 2011-08-30 : 07:46:28

Hi, thank you for your answer .. yes, a little homework exercise..

I run the following query:

select
Sales.BookName AS Name, Sales.MonthSales as Month,Sales.LibrarySales, Sales.BookStoreSales as StoreSales,
Plann.LibraryPlan, Plann.BookStorePlan as StorePlan
from Sales
full outer join Plann
on Sales.BookName=Plann.BookName and Sales.MonthSales=Plann.MonthPlan
the result is:

Name Month LibrarySales StoreSales LibraryPlan StorePlan
x 2010.11 5 1 NULL NULL
x 2010.12 2 3 10 3
y 2010.10 1 2 NULL NULL
y 2010.11 3 2 5 3
y 2010.12 4 1 5 3
NULL NULL NULL NULL 10 3
NULL NULL NULL NULL 5 3
NULL NULL NULL NULL 5 3
NULL NULL NULL NULL 5 3

I miss from result the months that exist in Plan table, but they are not into Sales table. How can I get all of them?

Thank you,
Adrian

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-30 : 08:52:52

Almost there. Check out ISNULL or COALESCE in your course handbook.

Both substitude a value if the tested value is NULL. ISNULL is SqlServer specific and COALESCE is an ANSI Standard

Here is an example that should work.

also -- you can enclose any code you write in code tags to preserve formatting. Put a LEFTSQUAREBRACE Code RIGHTSQUAREBRACE tag round the code.


SELECT
	COALESCE(s.[BookName], p.[BookName]) AS [bookName]
	, COALESCE(s.[MonthSales], p.[MonthPlan]) as [Month]
	, s.[LibrarySales]
	, s.[BookStoreSales]
	, p.[LibraryPlan]
	, p.[BookStorePlan]
FROM
	Sales AS s
	FULL OUTER JOIN Plann AS p ON s.[BookName] = p.[BookName] AND s.[MonthSales] = p.[MonthPlan]

Welcome to SQL Team btw.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources