JOIN and NULL values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

JOIN and NULL values

Author

Topic

associate01
Starting Member

3 Posts

Posted - 2007-09-20 : 11:58:53

I've read Books Online and everything re: NULL that I could get my hands on but I'm still a bit confused. Consider the following situation.

There are three tables, one for each type of user, structured as follows:

ID DateCreated
123 2007-09-07 19:44:24.233
124 2007-09-09 12:21:12.840
125 2007-09-11 17:36:53.641

My query should return:

DateCreated UserType1 UserType2 UserType3
2007-09-11 2 0 1
2007-09-10 0 0 2
2007-09-09 3 2 1
2007-09-08 1 2 0
2007-09-07 1 0 1

Right now, I have multiple SELECT statements, FULL JOIN'd together. Each SELECT statement returns a COUNT(*) and the date via CONVERT(VARCHAR, DateCreated, 102).

The problem is joining the records together. Several things are not as they should be. The below is a sample data dump from the query:

2007.09.20 1 NULL 0 2007.09.20 1 NULL 0
2007.09.19 3 NULL 0 2007.09.19 2 2007.09.19 2
2007.09.18 1 NULL 0 2007.09.18 1 2007.09.18 2
2007.09.16 17 2007.09.16 1 2007.09.16 11 NULL 0
NULL 0 NULL 0 NULL 0 2007.09.17 1
NULL 0 NULL 0 NULL 0 NULL 156
NULL 1 NULL 0 NULL 0 NULL 0

It is broken up into date/count columns for each of the types of users. As you can see they do not line up correctly.

Any ideas how to get around these NULL issues?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-20 : 13:01:22

post create table statements, sample data (preferably in the form of insert scripts)
and desired results based on the sample data.
also search around here for crosstabs

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-21 : 05:19:34

Hi
Why not use an INNER JOIN , if you only want to return matching records . Do you need to return all rows from RIGHT and LEFT tables?

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 05:43:23

Maybe this is what you want ?

DECLARE @ID1 TABLE
(
	ID 		int,	
	DateCreated	datetime
)
INSERT INTO @ID1
SELECT	123, '2007-09-07 19:44:24.233'	UNION ALL
SELECT	124, '2007-09-09 12:21:12.840'	UNION ALL
SELECT	125, '2007-09-11 17:36:53.641'

DECLARE @ID2 TABLE
(
	ID 		int,	
	DateCreated	datetime
)
INSERT INTO @ID2
SELECT	123, '2007-09-08 19:44:24.233'	UNION ALL
SELECT	124, '2007-09-10 12:21:12.840'	UNION ALL
SELECT	125, '2007-09-10 17:36:53.641'

DECLARE @ID3 TABLE
(
	ID 		int,	
	DateCreated	datetime
)
INSERT INTO @ID3
SELECT	123, '2007-09-07 19:44:24.233'	UNION ALL
SELECT	124, '2007-09-10 12:21:12.840'	UNION ALL
SELECT	125, '2007-09-13 17:36:53.641'

SELECT	[Date], UserType1 = SUM(cnt1), UserType2 = SUM(cnt2), UserType3 = SUM(cnt3)
FROM
(
	SELECT	[Date] = DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0), 
		cnt1 = COUNT(*), cnt2 = 0, cnt3 = 0
	FROM	@ID1
	GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0)

	UNION ALL

	SELECT	[Date] = DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0), 
		cnt1 = 0, cnt2 = COUNT(*), cnt3 = 0
	FROM	@ID2
	GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0)

	UNION ALL

	SELECT	[Date] = DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0), 
		cnt1 = 0, cnt2 = 0, cnt4 = COUNT(*)
	FROM	@ID3
	GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0)
) u
GROUP BY [Date]

/*
Date                                                   UserType1   UserType2   UserType3   
------------------------------------------------------ ----------- ----------- ----------- 
2007-09-07 00:00:00.000                                          1           0           1 
2007-09-08 00:00:00.000                                          0           1           0 
2007-09-09 00:00:00.000                                          1           0           0 
2007-09-10 00:00:00.000                                          0           2           1 
2007-09-11 00:00:00.000                                          1           0           0 
2007-09-13 00:00:00.000                                          0           0           1 
*/

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 06:01:52

or using FULL OUTER JOIN

SELECT	[Date]	= coalesce(a.Date, b.Date, c.Date), ISNULL(SUM(a.cnt), 0), ISNULL(SUM(b.cnt), 0), ISNULL(SUM(c.cnt), 0)
FROM
(
	SELECT	[Date] = DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0), cnt = COUNT(*)
	FROM	@ID1
	GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0)
) a
FULL OUTER JOIN
(
	SELECT	[Date] = DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0), cnt = COUNT(*)
	FROM	@ID2
	GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0)
) b	ON	a.Date	= b.Date
FULL OUTER JOIN
(
	SELECT	[Date] = DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0), cnt = COUNT(*)
	FROM	@ID3
	GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateCreated), 0)
) c	ON	a.Date	= c.Date
GROUP BY coalesce(a.Date, b.Date, c.Date)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources