Joining tables - SQL Server Forums

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

Joining tables

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-06 : 12:48:39

I have the tables below and need to return a NULL value for Customer ABC in 2009. I've tried a left outer join but that hasn't seemed to work. This is just a very small example as there are 100's of customers I need to add NULL values for in 2009. Any ideas?

Table Year Table Data
Year Customer Year Orders Shipments
2007 ABC 2007 5 1
2008 ABC 2008 10 2
2009 xyz 2007 20 4
xyz 2008 15 3
xyz 2009 75 15

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-06 : 12:58:23

Hard to give you quality advice with out know anything about your tables, data or query. But, it sounds like you will need to LEFT OUTER join to a Date table of some sort. That table could be a persistant table, temp table, table variable or it could be a derived table.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 13:02:19

use [ code] [ /code] tags...(w/out the space) to keep the formatting


Table Year		Table Data			
Year		Customer Year  Orders Shipments
2007		ABC	 2007	5	1
2008		ABC	 2008	10	2
2009		xyz	 2007	20	4
		xyz	 2008	15	3
		xyz	 2009	75	15

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-06 : 13:07:38

The tables above are correct. - Thanks x002548

My query is:
select *
from 'year' as y
left join 'data' as d on y.year = d.year

This query does not return anything for 2009 for customer ABC and that's where I need to know if and how I can add NULL or zero for customer ABC for the year 2009.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 13:16:37

[code]
SET NOCOUNT ON
GO

CREATE TABLE #Year ([Year] char(4))
GO

INSERT INTO #Year ([Year])
SELECT '2007' UNION ALL SELECT '2008' UNION ALL SELECT '2009'
GO

CREATE TABLE #Data (Customer char(3), [Year] char(4), Orders int, Shipments int)
GO

INSERT INTO #Data (Customer, [Year], Orders, Shipments)
SELECT 'ABC', '2007', 5, 1 UNION ALL
SELECT 'ABC', '2008', 10, 2 UNION ALL
SELECT 'xyz', '2007', 20, 4 UNION ALL
SELECT 'xyz', '2008', 15, 3 UNION ALL
SELECT 'xyz', '2009', 75, 15
GO

SELECT y.[Year], d.Customer, d.Orders, d.Shipments
FROM ( SELECT DISTINCT [Year], Customer FROM #Year CROSS JOIN (SELECT Customer FROM #Data) AS c) y
LEFT JOIN #Data d
ON y.[Year] = d.[Year] AND y.Customer = d.Customer
GO

DROP TABLE #Year, #Data
GO

SET NOCOUNT OFF
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 13:19:27

ooops


    SELECT y.[Year], y.Customer, d.Orders, d.Shipments
      FROM ( SELECT [Year], Customer FROM #Year CROSS JOIN (SELECT DISTINCT Customer FROM #Data) AS c) y
 LEFT JOIN #Data d
		ON y.[Year] = d.[Year] AND y.Customer = d.Customer
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-06 : 13:23:32

Thanks for the help. However, the tables I provided are just a small sample of the actual data I'm working with. There are a few hundred customers that don't have data for 2009 that I need NULL values for and the actual data table is roughly 18,000 lines. I believe your example above would require a massive amout of typing the UNION ALL's.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-06 : 13:33:47

quote:
Originally posted by TPie9

<snip> I believe your example above would require a massive amout of typing the UNION ALL's.

That is just setting up the sample data. Just replace the Temp table names with your actual table names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources