Joining table - 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)

Joining table

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 04:51:29

need help in weird joining...>"<

CREATE TABLE #category(
cat_code varchar(10))

CREATE TABLE #st_mast(
cat_code varchar(10)
quantity INT)

INSERT INTO #category
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'

INSERT INTO #st_mast
SELECT 'A', 48 UNION ALL
SELECT NULL, 5 UNION ALL
SELECT 'D', 74 UNION ALL
SELECT NULL, 4 UNION ALL
SELECT 'A', 42 UNION ALL
SELECT 'D', 42

DROP TABLE #category
DROP TABLE #st_mast

expected result
cat_code, quantity
A, 90
NULL, 9

Hope can help...but advise to wait pros with confirmation...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 04:57:06

One way:


select c.cat_code, sum(m.quantity) as quantity
from #category c
inner join #st_mast m
	on c.cat_code = m.cat_code
group by c.cat_code
union all
select cat_code, sum(quantity)
from #st_mast
where cat_code is null
group by cat_code

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-10 : 04:59:29

where exists ... OR cat_code is null

I think a join is not the solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 04:59:45

hmm...is there anyway other than UNION...union seem to be eating speed... btw all of this is just because of bad data

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-10 : 05:00:30

quote:

Originally posted by RickD

One way:


select c.cat_code, sum(m.quantity) as quantity
from #category c
inner join #st_mast m
	on c.cat_code = m.cat_code
group by c.cat_code
union all
select cat_code, sum(quantity)
from #st_mast
where cat_code is null
group by cat_code

or so

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-10 : 05:02:13

The problem is not that union it is your bad data!

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 05:03:11

Another way:


SELECT	m.cat_code, m.quantity
FROM	(
		SELECT	cat_code, quantity = SUM(quantity)
		FROM	#st_mast
		GROUP BY cat_code
	) m
	left JOIN #category c	ON	m.cat_code	= c.cat_code
WHERE	m.cat_code	IS NULL
OR	c.cat_code	IS NOT NULL

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

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-10 : 05:03:26

SELECT m.cat_code, SUM(quantity )
FROM #category c
FULL OUTER JOIN #st_mast m on c.cat_code = m.cat_code
WHERE (m.cat_code IN (SELECT cat_code FROM #category) OR m.cat_code is null)
GROUP BY m.cat_code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 05:03:58

quote:
Originally posted by webfred

The problem is not that union it is your bad data!

No, you're never too old to Yak'n'Roll if you're too young to die.

True true

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 05:05:05

waterduck,

you have more than one way there, so which method is faster ?

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 05:06:18

hold on...im creating more sample data to test it out...

Hope can help...but advise to wait pros with confirmation...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 05:08:47

quote:
Originally posted by khtan

waterduck,

you have more than one way there, so which method is faster ?

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

Yours is according to the execution plan.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 05:11:57

[code] |RICKD SOLUTION |khtan SOLUTION |asgast SOLUTION(after modification by khtan)
cached plan size |24b |17b |20b
subtree cost |0.0218304 |0.0180682 |0.0185847
estimated row |2 |7 |1[/code]
edit = add asgast query

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 05:13:43

quote:

Originally posted by asgast

SELECT m.cat_code, SUM(quantity )
FROM #category c
FULL OUTER RIGHT JOIN #st_mast m on c.cat_code = m.cat_code
WHERE (m.cat_code IN (SELECT cat_code FROM #category) OR m.cat_code is null)
GROUP BY m.cat_code

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 05:14:14

now is the following question!
VERY IMPORTANT
how would you add in range?
cat_code >='' and cat_code <=''
since NULL is > '' when the user select cat_code >='a' and cat_code <='b', NULL value will be shown!!!

ps. this is where i stuck with...at first i used rick union to solve my null problem...

Hope can help...but advise to wait pros with confirmation...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 05:24:08

Are you saying that now you do not want to show nulls?!?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 05:25:32

[code]
DECLARE @start_code varchar(10),
@end_code varchar(10)

SELECT @start_code = 'A', -- SET NULL FOR no limit
@end_code = 'A'

SELECT m.cat_code, m.quantity
FROM (
SELECT cat_code, quantity = SUM(quantity)
FROM #st_mast
GROUP BY cat_code
) m
left JOIN #category c ON m.cat_code = c.cat_code
WHERE ( @start_code IS NULL OR m.cat_code >= @start_code )
AND ( @end_code IS NULL OR m.cat_code <= @end_code )
AND ( m.cat_code IS NULL OR c.cat_code IS NOT NULL )
[/code]

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 05:26:09

null or not nulls are depending to the cat_code range.
cat_code >= '' and cat_code <= 'zzzzzz'<-- will show nulls
cat_code >= 'a' and cat_code <= 'zzzzz'<-- will not show nulls

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 05:30:53

mr.tan....@start_code = '' or @start_code = NULL also won't show NULL rows out.

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 05:34:39

[code]
SELECT m.cat_code, m.quantity
FROM (
SELECT cat_code, quantity = SUM(quantity)
FROM #st_mast
GROUP BY cat_code
) m
left JOIN #category c ON m.cat_code = c.cat_code
WHERE ( @start_code IS NULL OR m.cat_code >= @start_code )
AND (
@end_code IS NULL
OR m.cat_code <= @end_code
OR (@start_code IS NULL AND m.cat_code IS NULL )
)
AND ( m.cat_code IS NULL OR c.cat_code IS NOT NULL )
[/code]

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 05:36:11

wahahaha you save my day my god!

Hope can help...but advise to wait pros with confirmation...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources