return data even if not in both tables

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

return data even if not in both tables

Author

Topic

lightspd
Starting Member

6 Posts

Posted - 2009-09-17 : 02:42:23

Hi all,

This is probably a simple question, but I just can't seem to figure it out. I'm selecting data from two tables. Both tables have, date, hour and count. Now the problem is table1 will have an hour and count for every hour in an 8 hour period, but table2 will only have an hour and count for some of the hours. What I want is if table1 has an hour table2 doesn't the results return 0 for table2 data and the count for table1.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 02:45:54

Use Left outer join

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 02:46:22

[code]
select t1.date, t1.hour, t1.count, isnull(t2.count, 0)
from table1 t1
left join table2 t2 on t1.date = t2.date
and t1.hour = t2.hour
[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 02:49:01

quote:

Originally posted by khtan


select t1.date, t1.hour, t1.count, isnull(t2.count,0)
from   table1 t1
       left join table2 t2 on  t1.date = t2.date 
                           and t1.hour = t2.hour

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

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 02:51:35

thanks Madhivanan

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

lightspd
Starting Member

6 Posts

Posted - 2009-09-17 : 03:21:01

Thank you that worked great. I did run into a second issue though. I forgot t2 has an extra column for location, but I don't care about the location. The problem now is that unless I use a sum and group by, I get to results that show date, time, count for each register. When I add the sum and group by though, the results don't include the null fields. Please let me know if that makes since.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 03:22:36

can you shows us your query ?

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

lightspd
Starting Member

6 Posts

Posted - 2009-09-17 : 03:44:41

sure, there's a reason for the converts, so try to ignore them.

select labor.STORE as store,
CONVERT(varchar(10),labor.date,112) as date,
datepart(hh,labor.date) as hour,
SUM(convert(int,LABOR.tranCOUNT)) AS TRANCOUNT,
SUM(VISIT.COUNT)AS visitors
from
table1 visit
left join
table2 labor
on
CONVERT(varchar(10), labor.date, 112) = visit.visDate and
convert(varchar(2),VISIT.visHOUR, 108) = datepart(hh,labor.date)

where labor.store = visit.store

GROUP BY labor.store,labor.DATE
order by convert(int,labor.store),labor.DATE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 04:02:03

try


select	labor.STORE as store,
	CONVERT(varchar(10),labor.date,112) as date,
	datepart(hh,labor.date) as hour,
	SUM(convert(int, LABOR.tranCOUNT)) AS TRANCOUNT,
	SUM(isnull(VISIT.COUNT,0)) AS visitors
from	visit
	left join labor	on	labor.store 				= visit.store
			and	CONVERT(varchar(10), labor.date, 112) 	= visit.visDate 
			and	datepart(hh, labor.date)		= convert(varchar(2),VISIT.visHOUR, 108)
GROUP BY labor.store, CONVERT(varchar(10),labor.date,112), datepart(hh,labor.date)
order by convert(int,labor.store), labor.DATE

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

lightspd
Starting Member

6 Posts

Posted - 2009-09-17 : 04:14:20

Still not working,it returns a total for all visitors with all the other fields being null. If I remove the sums and group by the results come back with what I need but in separate lines for each location(separate from store) from visit table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 04:25:22

can you post sample data and show us the required result ?

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

lightspd
Starting Member

6 Posts

Posted - 2009-09-17 : 04:34:02

table 1
store | date | count
1 | 12/22/09 15:00 | 5
1 | 12/22/09 17:00 | 7

table 2
store | date | hour | loc | count
1 | 12/22/09 | 15 | north | 6
1 | 12/22/09 | 15 | south | 2
1 | 12/22/09 | 17 | south | 1
1 | 12/22/09 | 12 | east | 3

now currently when I run the query my results are something like this
store | date | hour | trancount | visitor
1 | 12/22/09 | 15 | 5 | 8
1 | 12/22/09 | 17 | 7 | 1

but east will be left out. The wanted required output would be
store | date | hour | t | v
1 | 12/22/09 | 15 | 5 | 8
1 | 12/22/09 | 17 | 7 | 1
1 | 12/22/09 | 12 | 0 | 3

Hope this helps

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 04:54:42

[code]
DECLARE @table1 TABLE
(
store int,
[date] datetime,
[COUNT] int
)
INSERT INTO @table1
SELECT 1 , '12/22/09 15:00' , 5 UNION ALL
SELECT 1 , '12/22/09 17:00' , 7

DECLARE @table2 TABLE
(
store int,
[date] datetime,
[hour] int,
loc varchar(5),
[COUNT] int
)

INSERT INTO @table2
SELECT 1 , '12/22/09' , 15 , 'north' , 6 UNION ALL
SELECT 1 , '12/22/09' , 15 , 'south' , 2 UNION ALL
SELECT 1 , '12/22/09' , 17 , 'south' , 1 UNION ALL
SELECT 1 , '12/22/09' , 12 , 'east' , 3

SELECT store = coalesce(t1.store, t2.store),
[date] = coalesce(DATEADD(DAY, DATEDIFF(DAY, 0, t1.[date]), 0), t2.[date]),
[hour] = coalesce(DATEPART(hour, t1.[date]), t2.[hour]),
trancount = ISNULL(t1.[COUNT], 0),
visitor = t2.[COUNT]
FROM @table1 t1
FULL OUTER JOIN
(
SELECT store, [date], [hour], [COUNT] = SUM([COUNT])
FROM @table2
GROUP BY store, [date], [hour]
) t2 ON t1.store = t2.store
AND DATEADD(DAY, DATEDIFF(DAY, 0, t1.[date]), 0) = t2.[date]
AND DATEPART(hour, t1.[date]) = t2.[hour]
ORDER BY [store], [date], [hour]

/*
store date hour trancount visitor
----------- ------------------------------------------------------ ----------- ----------- -----------
1 2009-12-22 00:00:00.000 12 0 3
1 2009-12-22 00:00:00.000 15 5 8
1 2009-12-22 00:00:00.000 17 7 1

(3 row(s) affected)
*/
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 04:56:47

OR if you do not have record in table1 but not in table2 then use RIGHT JOIN or LEFT JOIN

-- LEFT JOIN


SELECT	store		= t2.store,
	[date]		= t2.[date],
	[hour]		= t2.[hour],
	trancount	= ISNULL(t1.[COUNT], 0),
	visitor		= t2.[COUNT]
FROM	(
		SELECT	store, [date], [hour], [COUNT] = SUM([COUNT])
		FROM	@table2 
		GROUP BY store, [date], [hour]
	) t2	
	LEFT JOIN @table1 t1	ON	t1.store					= t2.store
				AND	DATEADD(DAY, DATEDIFF(DAY, 0, t1.[date]), 0)	= t2.[date]
				AND	DATEPART(hour, t1.[date])			= t2.[hour]
ORDER BY [store], [date], [hour]

-- RIGHT JOIN


SELECT	store		= t2.store,
	[date]		= t2.[date],
	[hour]		= t2.[hour],
	trancount	= ISNULL(t1.[COUNT], 0),
	visitor		= t2.[COUNT]
FROM	@table1 t1
	RIGHT JOIN 
	(
		SELECT	store, [date], [hour], [COUNT] = SUM([COUNT])
		FROM	@table2 
		GROUP BY store, [date], [hour]
	) t2	ON	t1.store					= t2.store
		AND	DATEADD(DAY, DATEDIFF(DAY, 0, t1.[date]), 0)	= t2.[date]
		AND	DATEPART(hour, t1.[date])			= t2.[hour]
ORDER BY [store], [date], [hour]

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

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 06:22:48

When should you use a 'Full' Join?

I would use a temporary table for everything ever

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 06:28:47

when you have records in the left table not in the right table and vice versa

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

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-17 : 07:02:25

But you can still use left or right joins to GUIDS if that is the case? Could you explain exactly what it does? (just for clarity sake, I can guess what it does from the FULL keyword lol xD)

Thanks for your help xD

I would use a temporary table for everything ever

lightspd
Starting Member

6 Posts

Posted - 2009-09-18 : 01:06:51

thank you, got it working great now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources